Query Execution Time Variance

Question

Post reply

Query Execution Time Variance

Ed Zann

SSCrazy Eights

Points: 8694
More actions
March 2, 2010 at 3:09 pm

#139109

I have a read-only database that is a nightly snapshot of our core system DB2 database. It is created by our vendor process and does not have indexes on tables but does compute statistics on selected columns.
I need to limit the query to loan records with an interest rate code of 1 or 2 ( only 1,2,3 are possible).
The field is a char(1) and it does not have statistics available.
If I say
AND (IntRateCode = '1' OR IntRateCode = '2') the query runs forever.
Ditto if I use
AND IntRateCode < '3'
However, if I use
AND IntRateCode = '1'
I get results back in a couple of seconds.
What might be going on here?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Are most of the records 2's?

Run this:

SELECT IntRateCode, COUNT(*)

FROM yourtable

GROUP BY IntRateCode

Post the result.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Without an index, you're seeing table scans. If you had an index, I'm fairly sure the '< 3' operation would work well (depending on the distribution of the data). It's possible that because you only have a three values to filter on that you're going to get scans no matter what.

Have you looked at the execution plans for the different behaviors of the query?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

yes Grant is right.

Execution plan will give clear picture.

can you post table schema and execution plan?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 4

Unfortunately, this is a banking system, and I would not be able to post the query or schema without obfuscating the table / column names (which I don't have time to do at athe moment). I have looked at the execution plans and they are somewhat different when the query uses = versus when I use < or BETWEEN or IN. In either case the major cost is a table scan of a very wide loan table with 150,000 rows.

I have decided instead to use a UNION query and change only the rate code criteria =1 or =2. This gets me a result back in 3 secs. which is acceptable. Maybe not the most elegant solution, but it gets the job done.

It's time to move on. I thank you for your replies - the mystery remains to be solved another day.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

Before you simply "move on"... what would be wrong with simply adding the creation of the correct indexes to the process?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 6

Re: Indexes

These nightly snapshot db's are created as read-only by our core bank system. We run hundreds of reports and data extracts from them. This is really the first time I have run into such a quirky issue as described. Generally the queries run in a 30 seconds or less. Anyway I would be hard pressed to build a case for messing with the snapshots as supplied by the core system to fix a problem with one report. Our bank examiners and internal audit folks would have me under the bright lights explaining exactly how am I changing the database. If I had a bigger dragon to slay I might go there, for this, I think not.