Speed

kilika · June 30th, 2003, 04:33 PM

My SQL Prodedures are running very slow. I have tried doing an index, but I am afraid I do not understand them very well. Is there somehting I can do to see why it takes them so long? Does anyone have any tips on improving the speed. These database tables have several hundred thousand records in them each. It is just getting slower and slower. I am not sure where I should go next on this. Any help is greatly appreciated.

Jeff Mason · June 30th, 2003, 07:03 PM

Run the query in Query Analyzer, but before you do, click 'Show Execution Plan' on the Query menu.

Execute the query, then inspect the query plan and see if you see any 'Table Scan' operations on your large tables. If you do, consider creating an index on the column involved in the operation which is forcing the table scan. A table scan is a sequential read of all the rows in a table, so avoiding these on big tables is prudent.

You could also try the 'Index tuning wizard' in Enterprise Manager. Select your server, then select 'Wizards...' from the tools menu, then select the Index Tuning Wizard under the management group in the dialog which displays.

This wizard requires that you run the SQL profiler in order to obtain a trace of the work your query does.

It can be a fair amount of work to set up, and my experience has been that I have been underwhelmed by its analysis, but YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

melvik · June 30th, 2003, 11:08 PM

Do what Jeff said! And also do not name ur SP wtarting with "sp_" bc it confuse with SYSTEM SP's & ...

Always:),
Hovik Melkomian.

kilika · July 1st, 2003, 10:52 AM

Some of them have seemed to speed up. I am still working on a few of them. One said Clustered index scan (17%), but it went pretty fast.

Are the clustered indexed what I want?

Here is one of my indexes:

Code:

CREATE NONCLUSTERED
INDEX billet_Chem_Index
ON Billet_Chem([Heat Number] ASC)
WITH 
DROP_EXISTING

Is this written properly?
Can I have several indexes on different columns in the same table?
If so, is it a smart thing to do?

Unfortunately some of the Stored Procs start with "sp_" though they were done before I got here. I am slowly redoing them and eliminating the ones that start with "sp_".

Thanks for all the advice and help.

Chris

kilika · July 1st, 2003, 11:52 AM

It looks like my left and right joins take 15%-20% and then I ahve one that says "Hash Match/Aggregate" that takes 47%. This query took a minute and 47 seconds. There were 2 table scans on small tables I join with but they took only 3% between the 2 of them.

Is it better to do a:

Code:

sum(col1 + col2 + col3)

in my select list in the SQL string or is it better to wait till I get it in the ASP and then add it like this:

Code:

rs("col1").value + rs("col2").value + rs("col3").value

?

Are there any other ideas out there on improving my sql speed?

Chris

Jeff Mason · July 1st, 2003, 12:16 PM

Quote:

quote:Originally posted by kilika
It looks like my left and right joins take 15%-20% and then I ahve one that says "Hash Match/Aggregate" that takes 47%. This query took a minute and 47 seconds. There were 2 table scans on small tables I join with but they took only 3% between the 2 of them.

The presence of a hash join (an optimizer term) generally means you are doing a JOIN on a column that has no useful index in either table. You might want to consider creating an index on these columns.

Quote:

quote:
Is it better to do a:

Code:

sum(col1 + col2 + col3)

in my select list in the SQL string or is it better to wait till I get it in the ASP and then add it like this:

Code:

rs("col1").value + rs("col2").value + rs("col3").value

?

6 of 1, half a dozen of the other, IMO. Unless you need those individual values returned to the client, I would tend to do the sum in the query. Since recordsets are big fat objects, I like to keep them as small as possible.

(Note that your two examples as written aren't equivalent, by the way.)

Quote:

quote:
Are there any other ideas out there on improving my sql speed?

Go get yourself a copy of "Inside SQL Server 2000" by Kalen Delaney and read chapters 14,15,16, and 17. They discuss query performance and tuning. You'll find it is not a simple subject

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · July 1st, 2003, 12:35 PM

Can anyone tell me the differences in what the following 2 code sections do?:

Code:

CREATE NONCLUSTERED
INDEX billet_Chem_Index
ON Billet_Chem([Heat Number] ASC)
WITH 
DROP_EXISTING


CREATE NONCLUSTERED 
INDEX [Billet_Chem7] 
ON [dbo].[Billet_Chem] ([HistoryKey] ASC, [SampleDate] ASC, [SampleTime] ASC, [Prod_Grade] ASC, [Heat Number] ASC )

I guess I do not understand what the DROP_EXISTING has to do with it and why the first (the one I wrote) has 1 column and the other (created by the tuning wizard) has 5 columns listed.

Does it do all 5 columns in 1 index or are they 5 seperate indexes?

Can anyone explain it to me?

Chris

Jeff Mason · July 1st, 2003, 12:54 PM

Quote:

quote:Originally posted by kilika
Can anyone tell me the differences in what the following 2 code sections do?:

Code:

CREATE NONCLUSTERED
INDEX billet_Chem_Index
ON Billet_Chem([Heat Number] ASC)
WITH 
DROP_EXISTING


CREATE NONCLUSTERED 
INDEX [Billet_Chem7] 
ON [dbo].[Billet_Chem] ([HistoryKey] ASC, [SampleDate] ASC, [SampleTime] ASC, [Prod_Grade] ASC, [Heat Number] ASC )

I guess I do not understand what the DROP_EXISTING has to do with it and why the first (the one I wrote) has 1 column and the other (created by the tuning wizard) has 5 columns listed.

Does it do all 5 columns in 1 index or are they 5 seperate indexes?

Can anyone explain it to me?

Chris

The DROP_EXISTING is used to recreate an index. It implies that the named index already exists. It tends to be more useful if used on the clustered index; dropping and recreating the clustered index forces dropping and recreating all of the nonclustered indexes as well, and that can be handy.

You drop and recreate an index if significant updates have been made over time to the table so that the indexes become fragmented. Dropping and recreating them compacts them.

Creating an index with 5 columns is not at all the same as having 5 separate indexes. The order of the columns in a multicolumn index is very important, as queries can only use the index if selections/joins are done in the same order.

It's kinda like a phone book. This is in order by last name, then first name within last name. Great for looking up somebody if you know their full name, useful if you know only their last name, and totally worthless if you only know their first.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

kilika · July 1st, 2003, 01:04 PM

Thanks for the great information. I just ordered the book as well and look forward to understanding them.

Thanks agian for the great help.

Chris

kilika · July 1st, 2003, 05:21 PM

Is it better to have several columns specified in 1 index, have each column in its own index, or both?

Chris