February 12, 2010 at 7:26 am
Table has 5681566 rows. Schema as follows
CREATE TABLE [dbo].[PAIRHIST](
[ID] [int] IDENTITY(1,1) NOT NULL,
[TICKER1] [varchar](50) NULL,
[TICKER2] [varchar](50) NULL,
[JOINTICKER] [varchar](100) NULL,
[date] [datetime] NULL,
[PRICE_LAST] [float] NULL,
[PRICE_OPEN] [float] NULL,
[PRICE_HIGH] [float] NULL,
[PRICE_LOW] [float] NULL,
[Kst] [float] NULL,
[dst] [float] NULL,
[wma] [float] NULL,
[INTRADAY] [smallint] NULL,
[UNIVERSE] [varchar](20) NULL,
[PERIOD] [varchar](1) NULL,
CONSTRAINT [PK_PAIRHIST] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IDX_DATE] ON [dbo].[PAIRHIST]
(
[date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
I am running the following two Queries
Query 1
UPDATE PAIRHIST SET PAIRHIST.PRICE_LAST=A.PRICE_LAST FROM (SELECT PRICE_LAST,JOINTICKER FROM PAIRHIST WHERE PERIOD='D' AND DATE='2010-02-10') as A WHERE PAIRHIST.JOINTICKER=A.JOINTICKER AND PAIRHIST.PERIOD='W' AND PAIRHIST.DATE='2010-02-10'
Query 2
UPDATE PAIRHIST SET PAIRHIST.PRICE_LAST=A.PRICE_LAST FROM (SELECT PRICE_LAST,JOINTICKER FROM PAIRHIST WHERE PERIOD='D' AND DATE='2010-02-11') as A WHERE PAIRHIST.JOINTICKER=A.JOINTICKER AND PAIRHIST.PERIOD='W' AND PAIRHIST.DATE='2010-02-11'
Both queries update the same number of rows (150161) except that the select statement subquery in Q2 brings back all NULL Values to insert into the field (PAIRHIST.PRICE_LAST) to be updated. They are almost identical except for the date value
Q1 runs in 3 secs
Q2 is still running after 45mins!
Still waiting for it to finish so i can have a look at the query plan.
Any thoughts would be much appreciated.
thanks
February 12, 2010 at 7:36 am
Try updating statistics on the table.
UPDATE STATISTICS PAIRHIST WITH FULLSCAN
The index is on a ascending date column, it's a common problem on larger tables for the stats to be sufficiently out of date that queries for older rows work fine, but queries for newer rows don't.
When you get the execution plans, post them both please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2010 at 7:42 am
we run update stats with fullscan on the whole database every night. You are right though that should help but I fear if will continue to be a reoccurring problem. One interesting observation I made was in the execution plan on both the queries. i took the update statement out and ran a select instead like so
SELECT PAIRHIST.PRICE_LAST
FROM
PAIRHIST,
(SELECT PRICE_LAST,JOINTICKER FROM PAIRHIST WHERE PERIOD='D' AND DATE='2010-02-11') as A
WHEREPAIRHIST.JOINTICKER=A.JOINTICKER
AND PAIRHIST.PERIOD='W'
AND PAIRHIST.DATE='2010-02-11'
interestingly the Q2 plan uses key Lookup and the number of executions is 300202!
Will investigate further...
February 12, 2010 at 7:59 am
Does running a stats update now help?
From the looks of the exec plan, it will fix this completely.
There are systems that need to run stats updates on specific tables hourly, running it once a day may not be enough for this particular table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 12, 2010 at 8:06 am
I fixed it by creating a covering index
CREATE NONCLUSTERED INDEX [IX_TestIndex]
ON [dbo].[PAIRHIST] ([date])
INCLUDE ([JOINTICKER],[PRICE_LAST],[PERIOD])
GO
That got rid of the Key lookup and the query now take 3 seconds!
Thanks for your comments...
February 12, 2010 at 8:09 am
Did you notice a Missing index recomendation in your Q1 plan ?
anyway Q2 is the one that causes problems ...
It shouldn't matter .... but did you try this alternative ?
Update H
SET PRICE_LAST = H1.PRICE_LAST
from PAIRHIST H
inner join PAIRHIST H1
On H1.JOINTICKER = H.JOINTICKER
and H1.PERIOD = 'D'
and H1.DATE = H.DATE
and H.PERIOD = 'W'
and H.DATE = '2010-02-11'
Regarding the NULLS returned by Q2.
Probably column jointticker isn't unique ...
and not all PRICE_LAST columns have been provided with a non-null value for the date '2010-02-11'
If that is the case, your join will return multiple rows per H row, the row order is not guaranteed, so you may actually end up getting unreliable results ...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply