Pulling Records from SQL VERY SLOW 1

[Rexolio]

Hi all,

need help ASAP!!!!!! Have a database that I pull records from to display on an active server page. It is seriously slow whenever its pulling records. The records in the database only add up to about 46,000. Most times I'm only pulling 50 - 1000 records at a time via the ASP.

For the sake of helping you help me, know this: It is NOT a problem with the active server page. I use the same script on another ASP with another SQL database and there are no problems. Plus, I wasn't having any problems with this page a few weeks ago and have changed nothing. I HAVE however worked on the database, exported it to Access, exported from Access back to SQL and edited it some in SQL.

Any suggestions on how to compress it, optimize it or whatever else I can check will be helpful. I am not very up to speed with SQL and don't know much in regards to terminology, so please keep that in mind. I have "SQL for Dummies" but can't find anything thus far that can help.

Thanks,
Rexolio

 

[tlbroadbent]

Most of the time, the performance issues with queries is the lack of indexes or poor query design, perhaps both. Is the table indexed on the column(s) used as criteria for selecting the records? If not, add approipriate indexes. If it is indexed, reindex the table to see if performance improves.

Which version of SQL Server are you running? If you have SQL 7 or 2000, you can analyze the query with Query Analyzer and the Index Tuning Wizard. These tools will help determine bottlenecks and/or suggest proper indexes.

Check the following sources for more information on performance tuning.

http://www.sql-server-performance.com/articles.asp

http://members.home.net/tlbroadbent/sqlarticles.htm

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[ababbitt]

You will likely need to add an index to the table(s) effected by the queries.

You could either add the index to the tables directly, based on the search criteria in you query...simply put and an index to the table for main filter in your where clause.

Or you could use the Index Tuning Wizard in Query Analyzer.

Hope this helps.

 

[Rexolio]

Thanks Guys,

Here's my query string:
SELECT * FROM dbo.tblLeads WHERE City = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 OR City2 = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 OR City3 = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 OR City4 = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 OR City5 = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 OR City6 = 'Atlanta' AND CDate <= '10/8/2001' AND CDate >= '9/1/2001' AND Updates = 'Yes' AND Evry = 0 ORDER BY WeddingDate
Query Analyzer shows no problems. Pulls the results quickly.
Estimated Execution Plan shows: Select Cost: 0% <-- Sort Cost: 1% <-- Filter Cost: 0% <-- Table Scan Cost: 99%
Not sure what that means (above)

I use a lot of criteria above in my query. Not sure what should be indexed!!! Suggestions?

 

[AshleyRickards]

Index any field that you query. You can also specify the index(s) in your FROM line and the locking type.

FROM tblLeads (INDEX(indexname,indexname)NOLOCK)

The table scan cost means it's scanning the entire table or in this case a HEAP. Ashley L Rickards
SQL DBA

 

[Rexolio]

You said &quot;Index any field that you query&quot;, so in my case I should have more than one index? i.e., FROM tblLeads (INDEX(Evry,CDate,City,City1,City2,City3,City4,City5,City6)NOLOCK)???

Didn't think I could/should do that! Or can it just be ANY 1 field that is being used to run the query? Also, what does &quot;NOLOCK&quot; mean/used for?

Thanks,
Rexolio

 

[tlbroadbent]

If SQL pulls the results quickly, then the problem may lie elsewhere. Slow network? ASP problem? I know you said that ASP wasn't the problem. But it cannot be eliminated given the evidence that SQL runs the query quickly.

SL cannot be eliminated yet becasue queries often perform differently in Query Anayzer than when executed from other applications.

The analysis says you are scanning the table. No indexes are being used. Create an index on Cdate if one doesn't exist. If possible make it a clustered index. Reverse the order of your date criteria so SQL can more readily recognize the date range.

CDate >= '9/1/2001' AND CDate <= '10/8/2001'
OR
CDate BETWEEN '9/1/2001' AND '10/8/2001'

You could also create a non-clustered covering index on Cdate, City, City1, City2, ..., City6.

If possible, simplify the query.

SELECT * FROM dbo.tblLeads
WHERE ((CDate BETWEEN '9/1/2001' AND '10/8/2001')
AND Updates = 'Yes' AND Evry = 0)
AND (City = 'Atlanta'
OR City2 = 'Atlanta'
OR City3 = 'Atlanta'
OR City4 = 'Atlanta'
OR City5 = 'Atlanta'
OR City6 = 'Atlanta')
ORDER BY WeddingDate

Alternative query:

SELECT * FROM dbo.tblLeads
WHERE ((CDate BETWEEN '9/1/2001' AND '10/8/2001')
AND Updates = 'Yes' AND Evry = 0)
AND ('Atlanta' In (City,City2,City3,City4,City5,City6))
ORDER BY WeddingDate Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Rexolio]

Terry,

I'm trying to use your suggestions, but I am now receiving nothing in return for the query...its just blank. I haven't created an index yet because 1. I'm not sure how (primary key?) and 2. Its a &quot;smalldatetime&quot; field and not sure how that works. The info is obviously replicated and I thought an index has to contain unique info for each record.

When I run the script in Query Analyzer, here is the message I get:
Server: Msg 170, Level 15, State 1, Line 2
Line 3: Incorrect syntax near '('.

Here is what I'm trying to run:
SELECT * FROM dbo.tblLeads
((CreateDate BETWEEN '9/1/2001' AND '10/8/01')
AND Updates = 'Yes')
AND ('Atlanta' In (City,City2,City3,City4,City5))
AND (Evry = 0) OR
((CreateDate BETWEEN '9/1/2001' AND '10/8/01')
AND Updates = 'Yes')
AND ('Atlanta' In (City,City2,City3,City4,City5))
AND (Mst = 1) OR
((CreateDate BETWEEN '9/1/2001' AND '10/8/01')
AND Updates = 'Yes')
AND ('Atlanta' In (City,City2,City3,City4,City5))
AND (All = 2)

Thanks...you guys are by far the most helpful I've encountered on this site!!!

 

[AshleyRickards]

To create index's right click on the table in Enterprise Manager. Select All Tasks, Manage Index's.
Click New. As Terry stated above create a clustered index on the date field and index your remaining fields.

If you do not have a PK on this table you should create one.

I think Terry will help with syntax. Ashley L Rickards
SQL DBA

 

[Rexolio]

okay, I figured out how to do the index. Made &quot;CreateDate&quot; (formerly CDate) the index, left &quot;Create Unique&quot; unchecked, left &quot;Fill Factor&quot; at 0%, checked &quot;Create as Clustered&quot; and left unchecked &quot;Don't automatically recompute statistics&quot;

reran the query, still same error message.

 

[Rexolio]

What index options do I choose? does it matter what index name I give it? should it be the same name as the createdate index I created? can I include all of the city indexes together, or do they need to be a separate index for each?

 

[AshleyRickards]

OH .. one thing I see in your query, your missing the WHERE after your from.

The one index you clustered created is fine. Yes you can index all the fields in one Index. The name is up to you. The way I do it is:
IX_TblLeads_CityFields. Something descriptive but not to long.
Options: If table is updated and inserted you should pad the index and create a fill factor of 85% or 90%. This will help with pagew splitting.
Go to Books On Line and search on Indexing. It will help you understand more clearly. Ashley L Rickards
SQL DBA

 

[tlbroadbent]

Ashley is correct about the syntax error. You left off the WHERE. You can name indexes whatever you choose. You can create one index for all the cities together and/or an index for each individually. You don't need a primary key but can create one if you want. A primary key is a combination of Index and unique contraint. Indexes don't need to be unique unless you create a UNIQUE index or primary key. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Rexolio]

After all of your help (which I appreciate greatly) it appears that the problem with the slow retrieval is with the formatting of the page. This did not appear to be a problem before, but I guess the results are increasing now as the database builds. Basically, I have a formatted web page that returns the results and displays them in tables, almost like a spreadsheet. I tried removing all but one of the results fields and it still took forever. Yet when I removed all of the formatting leaving only the results and a simple <BR> line break to begin the next record, the results zapped onto the screen in a split second.

I NEED the formatting. Its all basic table and font tags...no images. Any suggestions on this?

 

[AshleyRickards]

Hmmm .. I'm not a specialist in that area. I'm sure there's someone here who is. Good luck! Ashley L Rickards
SQL DBA

 

[tlbroadbent]

I know that tables have been very slow to build in IE. Perhaps soneone in an ASP forum can provide some help with speed. One thing you might want to do is page the results. That is return 50 rows and display with a NEXT button which would retrieve the next 50. Check the following links for articles about paging.

http://gethelp.devx.com/techtips/asp_pro/10min/10min0100/10min0100.asp

http://www.asp101.com/samples/db_paging.asp

http://www.aspfaqs.com/aspfaqs/ShowFAQ.asp?FAQID=37

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Rexolio]

I have posted a message on this as well in the ASP forum on this site. I can't really page because the pages are for clients to print and they're used to printing it all at one time and don't want them having to page through and print. But thanks for you suggestions.

And thanks for everything else, believe it or not, it helped me quite a bit on some other issues!!!