Query problem ?

[jxc]

I have 2 queries which are almost identical except that the criteria is slightly different.

The first query Accountid = "Project1" returns 262,000 rows completes in 1 minute. ( Database has 9,000,000 rows )

The second query Accountid = "Project2" returns 182,000 rows completes in over 7 minutes.

As I stated the queries are identical other than the criteria. The Accountid is indexed and part of a clustered key.

Is the physical storage on the database causing the problem?

Stumped.....

 

[jxc]

Sorry, The Table has 9,000,000 rows.

 

[tlbroadbent]

There are several factors that could cause the results you see.

1) Are the results consistent if you run one query after the other repeatedly?

2) What is the cache hit ratio on the server?

3) Have you analyzed the server to see if any locking is occurring during the queries?

4) Do you have any joins in the query?

5) Are the index statistics up to date? Have you reindexed or rebuilt indexes recently? If not, index fragmentation may cause the difference.

6) Are you returning all records to a client PC? If yes, the cause of the slowdown may be network activity.

7) Is there other activity on the server while running the queries?

8) You state that the queries are nearly identical. Do they return the same columns? Is the returned row the same size in each query? Do the any of the queries have any group by, order by, TOP, Union, distinct clauses? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jxc]

Thanks for the response...

I'll try to answer the questions based on what I know now and I will check a few things that I have not thought about.

1. Absolutely consistent..

Query 1 will range from 1-1.5 mins
Query 2 will range from 5.5 - 7.5 mins

2. Cache Hit....I'm checking

3. No locking at all.

4. Yes.. but they are identical.

5. No. I will try this ASAP

6. No.

7. No Other Activity. I have monitored the activity while running my queries.

8. They return the exact same columns. The returned rows are the same size. They have a group by and order by clause but again identical. The only difference is the value in 1 "where" clause.

I will follow up with more detail answers to #2 and #5.

Thanks again

/jc

 

[jxc]

Hi Sorry I didn't get back sooner but too many projects and not enough hours.

It appears as though I may have 1 of 2 problems. Many rows added recently or statistics. As you suggested the indices may be the culprit. This table has increased its rows going from 1.5 million rows to over 8.5 millions rows due to a change in the database collection methodology ( this is a datawarehousing application ). This has occurred in the last 3 months. If this were the case would this cause the fragmentation of the index? And, what is the best solution? Update statistics? De-fragment the index? Is this simply done with the DB maintenance wizard available in SQL?

Thanks in advance
/jc

 

[tlbroadbent]

You can check fragmentation with DBCC SHOWCONTIG.

Example: Show fragmentation od three indexes of MyTable. The lower the value of Scan Density, the higher the level of fragmentation. See details in SQL BOL

DBCC SHOWCONTIG (MyTable,0) WITH FAST
DBCC SHOWCONTIG (MyTable,1) WITH FAST
DBCC SHOWCONTIG (MyTable,2) WITH FAST

I recommend using DBCC DBREINDEX to rebuild the clustered index and all other indexes will be rebuilt. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jxc]

Now I'm confused the stats seem to indicate that fragmentation is not a problem. Any additional thoughts.
/jc

DBCC SHOWCONTIG scanning 'BudgetsTable' table...
Table: 'BudgetsTable' (205399951); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 200203
- Extents Scanned..............................: 25378
- Extent Switches..............................: 25863
- Avg. Pages per Extent........................: 7.9
- Scan Density [Best Count:Actual Count].......: 96.76% [25026:25864]
- Logical Scan Fragmentation ..................: 0.50%
- Extent Scan Fragmentation ...................: 14.67%
- Avg. Bytes Free per Page.....................: 102.2
- Avg. Page Density (full).....................: 98.74%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

[tlbroadbent]

Is index 1 the index used in the queries? Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jxc]

Yes, There is only 1 index on this table.
I double checked the execution plan to ensure that it was using the index. All the where clauses point to elements in the index.

Thanks
/jc

 

[tlbroadbent]

Have you run the query in SQL Query Analyzer with IO and Time statistics turned on? Reviewing the actual stats sometimes proves more helpful than viewing the plan.

Add SET STATISTICS IO ON and SET STATISTICS TIME ON before select statemtn or set the current connection options on the Query menu. Terry L. Broadbent - Salt Lake City, UT
Home of the 2002 Winter Olympics (Feb 8-24)

http://www.saltlake2002.com

 

[jxc]

Just an update....

I have updated the statistics on the W/E and this was definitely the problem. My 5 combined queries (I run them sequentially) took approximately 29 mins on average on Friday. I reran this morning and they completed on average in 8 minutes.

Thanks again Terry for alot of troubleshooting tips.

/jxc