May 7, 2008 at 12:39 pm
Why does the fixed variable approach beneath take foreever to execute. The queries seem identical (It's a bit of a quick and dirty query for which I apologize)
-- Finished within milliseconds:
select top 1000 *,
(
select top 1 tstamp from serverlog y where y.tstamp > s.tstamp
and data like '%67,$hbeat%'
and (tstamp between '2008-05-06 03:00' and '2008-05-06 03:30')
order by tstamp
) as Next_HBeat
from serverlog s where (tstamp between '2008-05-06 03:00' and '2008-05-06 03:30')
and data like '%67,$hbeat%'
and not exists
(
select * from serverlog r where (r.tstamp between dateadd(s,1,s.tstamp) and dateadd(mi,2,s.tstamp))
and (tstamp between '2008-05-06 03:00' and '2008-05-06 03:30')
and data like '%67,$hbeat%'
)
-- Cancelled execution after 10 minutes of 50 % cpu on the database server:
declare @startd datetime
declare @endd datetime
set @startd = '2008-05-06 03:00'
set @endd = '2008-05-06 03:30'
select top 1000 *,
(
select top 1 tstamp from serverlog y where y.tstamp > s.tstamp
and data like '%67,$hbeat%'
and (tstamp between @startd and @endd)
order by tstamp
) as Next_HBeat
from serverlog s where (tstamp between @startd and @endd)
and data like '%67,$hbeat%'
and not exists
(
select * from serverlog r where (r.tstamp between dateadd(s,1,s.tstamp) and dateadd(mi,2,s.tstamp))
and (tstamp between @startd and @endd)
and data like '%67,$hbeat%'
)
May 7, 2008 at 12:46 pm
The only thing I can think of on this is parameter sniffing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 7, 2008 at 1:22 pm
Same here. Can you generate execution plans for the two queries? Estimated of course since you don't want to wait for 10+ minutes again.
"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
May 7, 2008 at 1:29 pm
I'd also be interested in seeing enough of the tables DDL, some sample data, and expected results so I could understand the query as well. Looks like you have two subqueries hitting the same table that is the target of the query itself. I guess I am having a slightly difficult time seeing what it is trying to return.
😎
May 7, 2008 at 1:43 pm
you might want to look at full text indexes
i have a database with a lot of varcha(255) columns i have to search through and full text indexing is a lot faster than using like and regular indexes
May 7, 2008 at 2:57 pm
What is the type of tstamp column?
Piotr
...and your only reply is slàinte mhath
May 8, 2008 at 1:44 am
Normally a hearthbeat is coming every 1 minute from multiple devices. I'm trying to find the events in the "raw data log" of a specific device for instance '164' (example query was device 67) where a HB is not within the timing limit. When I find one I'm trying to find out when the next HB did come by creating a sub querie and looking up the next HB.
This is the result:
SERVERLOG_IDIPPORTDATATSTAMPNext_HBeat
29251462399.21.240.1221212303:20:11,CRCVD,164,$HBEAT,2 2008-05-06 03:20:11.2202008-05-06 03:26:14.327
29252049399.21.240.1221212303:27:14,CRCVD,164,$HBEAT,2 03:27:14,CSENT,164,OK 2008-05-06 03:27:14.3732008-05-06 03:33:17.470
29252725399.21.240.1221212303:35:17,CSENT,77,OK 03:35:17,CRCVD,164,$HBEAT,2 03:35:17,CSENT,164,OK 2008-05-06 03:35:17.4832008-05-06 03:52:18.140
29254110399.21.240.1221212303:52:18,CRCVD,164,$HBEAT,2 2008-05-06 03:52:18.1402008-05-06 03:58:21.327
29254696399.21.240.1221212303:59:21,CRCVD,164,$HBEAT,2 2008-05-06 03:59:21.327NULL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[SERVERLOG]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[SERVERLOG](
[SERVERLOG_ID] [int] IDENTITY(1,1) NOT NULL,
127.0.0.1 [varchar](16) NOT NULL,
[PORT] [int] NOT NULL,
[DATA] [varchar](2048) NULL CONSTRAINT [DF_SERVERLOG_DATA] DEFAULT (null),
[TSTAMP] [datetime] NOT NULL CONSTRAINT [DF_SERVERLOG_TSTAMP] DEFAULT (getdate()),
CONSTRAINT [PK_SERVERLOG] PRIMARY KEY CLUSTERED
(
[SERVERLOG_ID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N'[dbo].[SERVERLOG]') AND name = N'IX_SERVERLOG')
CREATE NONCLUSTERED INDEX [IX_SERVERLOG] ON [dbo].[SERVERLOG]
(
[TSTAMP] ASC
) ON [PRIMARY]
GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'received data' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'SERVERLOG', @level2type=N'COLUMN', @level2name=N'DATA'
GO
EXEC dbo.sp_addextendedproperty @name=N'MS_Description', @value=N'log timestamp' ,@level0type=N'USER', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'SERVERLOG', @level2type=N'COLUMN', @level2name=N'TSTAMP'
Table data sample:
SERVERLOG_IDIPPORTDATATSTAMP
29520090399.21.240.1221212309:42:49,CSENT,217,OK 2008-05-08 09:42:46.633
29520089399.21.240.1221212309:42:49,CRCVD,217,$HBEAT,0 2008-05-08 09:42:46.460
29520088399.21.240.1221212309:42:48,CSENT,202,OK 2008-05-08 09:42:45.773
29520087399.21.240.1221212309:42:48,CRCVD,202,$HBEAT,0 2008-05-08 09:42:45.587
29520086399.21.240.1221212309:42:45,CSENT,219,OK 2008-05-08 09:42:43.243
May 8, 2008 at 1:45 am
It's a Datetime
May 8, 2008 at 9:31 am
Frank,
That sort of helps, but doesn't. The output sample at the top of the post doesn't match the sample data at the bottom. I am a visual type of person and it would help me if I could see how the data is transformed from source to destination. If you could mock up some data that you then run through your query to generate a sample output and provide both of those, I would greatly appreciate it. Doesn't need to be a lot, but enought to see what gets reported and what doesn't.
Thanks!
😎
October 20, 2009 at 10:40 am
I am having a very similar problem. I have an audit table that keeps track of inserts, deletes, and updates of all the tables in the database. It keeps track of the primary key, the field name, its old value, its new value,the audittype(UPDATE,INSERT,DELETE)and its modify date. When querying with a datetime variable the query takes 2+ minutes to run, if I insert the string for the date directly, it runs within 1 second.
DECLARE @AuditDate datetime
SET @AuditDate = '09/01/2009'
SELECT *
FROM AuditTable
where modifydate > @AuditDate
and tablename = 'tablename'
and pkid not in (select pkid from audit where modifydate > @AuditDate and tablename = 'tablename' and audittype = 'INSERT')
In particular, if I change just the select within the NOT IN inner query... then the query runs very fast, otherwise it chugs at it for minutes.
Thank you all for your input,
Justin Rassier
October 20, 2009 at 11:11 am
Try using "OPTIMIZE FOR" query hint. As mentioned before, it seems to be a parameter sniffing issue. When using variables instead of scalar values, SQL query engine is not using the value of the variable to select an optimal execution plan.
October 20, 2009 at 11:13 am
It sounds like a classic case of parameter sniffing. Try updating your statistics. If that doesn't resolve the issue, you may need to use an OPTIMIZE FOR hint on the stored procedure or one of the other parameter sniffing resolutions.
You migh try changing the query to use a LEFT JOIN and eliminate NULL values rather than the NOT IN clause. That can frequently result in better performance.
"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
October 28, 2009 at 8:43 am
Thought I would update with what I found out.
Turns out Grant's suggestion was the key here
Grant Fritchey (10/20/2009)
...You migh try changing the query to use a LEFT JOIN and eliminate NULL values rather than the NOT IN clause. That can frequently result in better performance.
I replaced the NOT IN clause with a LEFT join and selecting the null values to see if I could get a performance boost. Worked like a charm.
Thank you for your reply and helping me out!
Justin Rassier
October 28, 2009 at 8:45 am
Thanks for posting what worked. It's always good to know and it helps people who find the thread later.
"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
November 2, 2009 at 5:18 pm
Apologies for adding to what seems to be a resolved thread.. BUT! I was having a very, very similar issue.
My sp looked something like this:
CREATE sp_blahblah @month INTEGER (YYYYMM) AS
BEGIN
--use the @month variable to work out the start and end month from a
--customer defined calendar table
DECLARE @start SMALLDATETIME
DECLARE @end SMALLDATETIME
SELECT col1,col2,col3
FROM tableA
INNER JOIN tableB ON a1 = b1
WHERE tableA.date BETWEEN @start AND @stop
END
Now when I executed the above the query would take about 30 seconds to run. However when I hardcoded in the dates, I was looking at a fraction of the time and a completely different execution plan.
The resolution for me was to manually recalculate the statistics on each table I was using. Obviously this is something that would be handled in a maintenance plan on a production server. But it worked for me! And I searched all over the web for help and ended up right here at home.. 😀
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply