query speed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

query speed

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-25 : 12:42:36

I have a sql table that has over 65931 rows.

Most of the querying I do is by date but also counting the duration collumn

table has collunm d,t,duration -- d and t are varchars and not dates

what can I do to increase the speed of querying.
I moved it from access to sql as I thought it would be faster but it is still so slow

please advise

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-25 : 12:54:46

You could create a clustered index over {Date} column.
But you should really use DATETIME for date, not varchar!

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-25 : 13:11:32

the dates are in the format 2007-03-11

so can I just change the type to datetime and that's it or do I have to reformat?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-25 : 13:20:07

And you have separated date as d and time as t too?
Why?

If possible, concatenate d and t as one datetime column, and create a clustered index over the new column.

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-25 : 13:49:41

i'm not searching on the date so does it still matter?

and what sql could I do to update a new field called datetime with the varchar fields of d and t?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-25 : 14:15:56

can you help me with the syntax to do this?

I can't even add a field now -- I keep getting a timeout.
Isn't SQL able to work with a large amount of data?

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-25 : 14:29:54

would it be better for me to put each month in a separate table with join queries when I query more then one month?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-03-25 : 23:06:18

performance depends to some extent on the hardware, but a table with only 65931 rows is cake for sql server if things are designed and indexed properly, even if it's running on a 5 year old laptop.

Based on the fact that you are storing dates as strings, I would say is your design is flawed, but it's hard to know since you haven't provided details about exactly what query is slow, the DDL for the table, and what indexes you have.

last week I populated a table on a 2005 instance with 1.2 billion rows. (not a transactional table, after population it's only read from) it took (only) 1.5 hrs to build a clustered index on it!

www.elsasoft.org

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 00:47:53

ok i switched date and time to a date time field

here's my ddl -- please advise what I can do?
CREATE TABLE [dbo].[Stats](
[Type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Destination] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[T] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Duration] [int] NULL,
[D] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Extension] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Area] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[geographic] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[statsdatetime] [datetime] NULL
) ON [PRIMARY]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 04:37:13

quote:
Originally posted by esthera

i'm not searching on the date so does it still matter?

What?

In your first post, you wrote

quote:
Originally posted by esthera

Most of the querying I do is by date but also counting the duration collumn

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 04:45:23

i meant i'm now searching on teh datetime field not the varchar field

i just moved this over to sql enterprise and it seems to work much better - what could be the issue?
any other recommendations for optimizing?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 04:50:38

Run these command one at a time to verify that all is ok.

1) ALTER TABLE dbo.Stats ADD DT DATETIME
2) UPDATE dbo.Stats SET DT = CAST(D + ' ' + T AS DATETIME)
3) select D, T, DT FROM dbo.Stats
4) ALTER TABLE dbo.Stats DROP COLUMN D
5) ALTER TABLE dbo.Stats DROP COLUMN T

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 05:00:26

i did those steps -- so does that mean it's optimized as best as possible?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 05:02:53

Which indexes do you have at present time?

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 05:09:55

CREATE CLUSTERED INDEX statdate ON stats (statsdatetime asc)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-26 : 05:19:15

What kind of queries do you rung against this table?
Have you noticed a speed difference yet?

Peter Larsson
Helsingborg, Sweden

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2007-03-26 : 05:27:55

well i switched to a different sql server and that alone is a big difference

my queries are basically searching between 2 dates and counting durations --
is it faster if I make this a view or stored procedure passing in the date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources