After migrating to 2005, I started noticing some performance problems with my queries. Here is one of them. The query takes some 40 seconds to execute for the first time with intense disk operation. Next time I run it the result comes back in less than a second. however, if I make a minor change to one of the parameter, it takes ~25 seconds with 100% CPU utilization and 0% of disk utilization. So, I'm assuming that all data has been cached, but I can't imagine why it takes so long to retrieve data from the memory?
BTW execution plans look completely identical for those queries.
Here is the query example:
select quotes.quote_id from indicators indicators_1, quotes, quotes quotes_1, quotes quotes_2, ranges
ranges_1 where not (1. * quotes_1.volume / quotes_2.volume < 3.54 and
indicators_1.RngStdDev25 / ranges_1.RngSMA25 < 1.2 ) and
quotes.quote_id - 1 = indicators_1.quote_id and quotes.quote_id - 1 = quotes_1.quote_id and
quotes.quote_id - 2 = quotes_2.quote_id and quotes.quote_id - 1 = ranges_1.quote_id and quotes.quote_id in
(select quote_id from trades where max_period = 2 and profit_target = 0.1 and strategy_id = 228)
And the parameter I'm changing is in red.
I will really appreciate if someone can help as I've started thinking about moving back to 2000 where no such problems existed.
Andy
I figured out that what actually takes so long is compiling the query. In some cases adding "option (fast 1)" helps, but in others doesn't.
Hell, I really have no idea why compiling the query but not retrieving the actual data is taking so much time.
some more food for thoughts... the query below takes ~10 seconds to compile
all tables have primary key on quote_id column:
select quotes.quote_id
from quotes inner join indicators indicators_1 on quotes.quote_id = indicators_1.quote_id
inner join ranges ranges_1 on quotes.quote_id = ranges_1.quote_id
inner join prices prices_1 on quotes.quote_id = prices_1.quote_id
where quotes.quote_id in (select quote_id from trades where max_period = 2 and profit_target = 0.1 and strategy_id = 228)
option (recompile)
So, now here's something that anyone can reproduce. Seems to be a bug (or feature) in SQL Server 2005.
The following script creates the tables and populates them with data:
CREATE TABLE [dbo].[test1](
[quote_id] [int] NOT NULL,
[ClSMA3] [real] NULL,
[ClSMA5] [real] NULL,
[ClSMA10] [real] NULL,
[ClSMA25] [real] NULL,
[ClSMA50] [real] NULL,
[ClMin3] [real] NULL,
[ClMin5] [real] NULL,
[ClMin10] [real] NULL,
[ClMin25] [real] NULL,
[ClMin50] [real] NULL,
[ClMax3] [real] NULL,
[ClMax5] [real] NULL,
[ClMax10] [real] NULL,
[ClMax25] [real] NULL,
[ClMax50] [real] NULL,
[LoMin3] [real] NULL,
[LoMin5] [real] NULL,
[LoMin10] [real] NULL,
[LoMin25] [real] NULL,
[LoMin50] [real] NULL,
[HiMax3] [real] NULL,
[HiMax5] [real] NULL,
[HiMax10] [real] NULL,
[HiMax25] [real] NULL,
[HiMax50] [real] NULL,
[LoMinBar3] [int] NULL,
[LoMinBar5] [int] NULL,
[LoMinBar10] [int] NULL,
[LoMinBar25] [int] NULL,
[LoMinBar50] [int] NULL,
[HiMaxBar3] [int] NULL,
[HiMaxBar5] [int] NULL,
[HiMaxBar10] [int] NULL,
[HiMaxBar25] [int] NULL,
[HiMaxBar50] [int] NULL
)
CREATE CLUSTERED INDEX [pk_test1] ON [dbo].[test1]
(
[quote_id] ASC
)
CREATE TABLE [dbo].[test2](
[quote_id] [int] NOT NULL,
[max_period] [smallint] NOT NULL,
[profit_target] [real] NOT NULL,
[trade_action] [smallint] NULL,
[open_date] [smalldatetime] NULL,
[open_price] [smallmoney] NULL,
[close_date] [smalldatetime] NULL,
[close_price] [smallmoney] NULL,
[strategy_id] [int] NULL,
[flag] [tinyint] NULL
) ON [PRIMARY]
DECLARE @.C INT
SET @.C = 0
WHILE (@.C < 13000)
BEGIN
INSERT INTO test2 (quote_id, max_period, profit_target) values (@.C * 290, 1, 1.0)
SET @.C = @.C + 1
END
SET @.C = 0
WHILE (@.C < 3800000)
BEGIN
INSERT INTO [test].[dbo].[test1]
([quote_id]
,[ClSMA3]
,[ClSMA5]
,[ClSMA10]
,[ClSMA25]
,[ClSMA50]
,[ClMin3]
,[ClMin5]
,[ClMin10]
,[ClMin25]
,[ClMin50]
,[ClMax3]
,[ClMax5]
,[ClMax10]
,[ClMax25]
,[ClMax50]
,[LoMin3]
,[LoMin5]
,[LoMin10]
,[LoMin25]
,[LoMin50]
,[HiMax3]
,[HiMax5]
,[HiMax10]
,[HiMax25]
,[HiMax50]
,[LoMinBar3]
,[LoMinBar5]
,[LoMinBar10]
,[LoMinBar25]
,[LoMinBar50]
,[HiMaxBar3]
,[HiMaxBar5]
,[HiMaxBar10]
,[HiMaxBar25]
,[HiMaxBar50])
VALUES
(@.C
,@.C/2
,@.C/3
,@.C/4
,@.C/5
,@.C/6
,@.C/7
,@.C/8
,@.C/9
,@.C/10
,@.C/11
,@.C/12
,@.C/13
,@.C/14
,@.C/15
,@.C/16
,@.C/17
,@.C/18
,@.C/19
,@.C/20
,@.C/21
,@.C/22
,@.C/23
,@.C/24
,@.C/25
,@.C/26
,@.C/27
,@.C/28
,@.C/29
,@.C/30
,@.C/31
,@.C/32
,@.C/33
,@.C/34
,@.C/35
,@.C/36)
SET @.C = @.C + 1
END
--
The query below takes too long to compile (10 seconds in my environment), however in 2000 returns in less than a second
select count(test2.quote_id)
from test2
where
test2.quote_id in (select quote_id from test1)
and test2.quote_id - 1 in (select quote_id from test1)
and test2.quote_id - 2 in (select quote_id from test1)
and test2.quote_id - 3 in (select quote_id from test1)
and test2.quote_id - 4 in (select quote_id from test1)
and test2.quote_id - 5 in (select quote_id from test1)
The query below takes forever to execute. Unless you change index pk_test1 from CLUSTERED to NONCLUSTERED. In such case it executes in fraction of a second...
select test2.quote_id
from test2
inner join test1 on test1.quote_id >= test2.quote_id - 6 AND test1.quote_id <= test2.quote_id
group by test2.quote_id
having count(*) = 7
|||
Hi, Andy! I'm glad you've provided a repro case; I was about to write a long note full of questions about your tables, row counts, indexes, and so on.
While I'm waiting for the INSERT script to run, I'm wondering if it is by design that your repro script doesn't have any indexes on test2.
|||Mike Blaszczak wrote:
While I'm waiting for the INSERT script to run, I'm wondering if it is by design that your repro script doesn't have any indexes on test2.
Hi Mike,
Thank you very much for your help. As to your question, in my original schema I had clustered index for quote_id column on test2 table, but it doesn't seem to affect those queries, this is why I didn't include it. Especially since there are not too many records (13K) in this table.
|||Record count isn't really relevant; you want to have an index to provide a better access path. If you have an index on the quote_id field of [test2] and the server needs to scan all the quote_id fields, it can do so by reading the index and densely reading only the quote_id fields. Without the index, it has to read the table pages which include a lot of other data besides the needed quote_id field. It does a bunch more I/O that way.
Unfortunately, my machine to test things for other people is dying; the C drive is starting to throw read errors, and so I'm not sure how much more I'll be able to help.
Your queries degenerate to six-way joins. What I believe is happening is that, in 2005, we see a very high query cost so the QO tries to run longer in order to look for a better plan -- one that's cheaper. The QO spends its time looking at all the alternatives to try to find a better way through the query.
It's disappointing that there so much time spent compiling for your query, but anyone who's benefited from the QO eventually finding a better plan certainly thinks that the extra compile time is a feature.
You've apparently tried to find a better way though the query, yourself; writing different SQL that should get the same result. I don't think there's anything you can do to make the query look better--there's no way to help the server know the correlation between the query_id values in one row and the query_id-6 values in the other rows. I've played around with adding computed columns to hold the range, then creating an index on those:
ALTER TABLE TEST2 ADD
MinMatch AS (Quote_ID - 5),
MaxMatch AS (Quote_ID)
select test2.quote_id
from test2
inner join test1 on test1.quote_id between test2.MinMatch AND test2.MaxMatch
but I'm afraid I can't say if it is performing any better as the machine I've been using to play with my queries has a failing drive, and that makes any measurements I take rather invalid.
You might try investigating a query that uses a loop and a cursor; this may be one instance where you can pull off better performance with a cursor than with set-based operations. Or, see what you can do with your model.
Also, please be sure you have current statistics on the tables. I've seen some bad cardinality estimates on my machine, but I can't investigate further with the failing drive and all ...
|||Thank you so much Mike for such detailed analysis.
yes record count does matter, as my queries take too long to compile only in case if there's many records in test2 table. What I'm going to do is switch back to SQL Server 2000 as I don't really need new features that come with 2005 but those performance issues are killing me.
Sorry to hear you've got hardware problems.
Thanks again and good luck
No comments:
Post a Comment