query assistance: below

Showing posts with label below. Show all posts

Wednesday, March 28, 2012

Query Help

I have a query below that returns a table with some client information along
with the last project manager that met with them and the date they met.
Nothing is included in the table if there are no meetings for that client.
Is there a way that I can still have the client information included even if
there are no meetings?

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T INNER JOIN
dbo.ContactView ON T.ContactIDNum =
dbo.ContactView.ContactIDNum
WHERE (T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799Karl A. Homburg (k-n-o-s-p-a-m-homburg@.upea.com) writes:
> I have a query below that returns a table with some client information
> along with the last project manager that met with them and the date they
> met. Nothing is included in the table if there are no meetings for that
> client. Is there a way that I can still have the client information
> included even if there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

For this type of query, it is always helpful to include CREATE TABLE
statements of your tables, and INSERT statements with sample data
and finally the desired output from the sample. Failing to provide
that increases the risk that you answer is based on a fair amount of
guesswork, like this suggestion:

SELECT T.ContactIDNum, C.Organization, C.Name,
C.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.ContactView C
LEFT JOIN dbo.MeetingView T
ON T.ContactIDNum = C.ContactIDNum
AND (T.[Date] = (SELECT MAX([Date])
FROM Meeting M
WHERE M.ContactIDNum = T.ContactIDNum))

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

>I have a query below that returns a table with some client information along
>with the last project manager that met with them and the date they met.
>Nothing is included in the table if there are no meetings for that client.
>Is there a way that I can still have the client information included even if
>there are no meetings?
>SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
>dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
>dbo.ContactView.ContactIDNum
>WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

Hi Karl,

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:

> I have a query below that returns a table with some client information along
> with the last project manager that met with them and the date they met.
> Nothing is included in the table if there are no meetings for that client.
> Is there a way that I can still have the client information included even if
> there are no meetings?
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T INNER JOIN
> dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))

try

SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
FROM dbo.MeetingView T
RIGHT JOIN dbo.ContactView ON T.ContactIDNum = dbo.ContactView.ContactIDNum
WHERE (T.[Date] IS NULL
OR T.[Date] =
(SELECT MAX([Date])
FROM Meeting
WHERE ContactIDNum = T.ContactIDNum))|||This one almost works. The only problem is that the ClientIDNum for the
rows that do not have any meetings shows up as null.

--
--
Karl A. Homburg
Electrical Engineer
U.P. Engineers & Architects, Inc.
100 Portage Street, Houghton, MI 49931
PH: (906) 482-4810 FX: (906) 482-9799
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:xw2983mmhlyy.1liq73j8yi7i1$.dlg@.40tude.net...
> On Wed, 25 Aug 2004 15:40:03 -0400, Karl A. Homburg wrote:
>> I have a query below that returns a table with some client information
>> along
>> with the last project manager that met with them and the date they met.
>> Nothing is included in the table if there are no meetings for that
>> client.
>> Is there a way that I can still have the client information included even
>> if
>> there are no meetings?
>>
>> SELECT T.ContactIDNum, dbo.ContactView.Organization,
>> dbo.ContactView.Name,
>> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
>> FROM dbo.MeetingView T INNER JOIN
>> dbo.ContactView ON T.ContactIDNum =
>> dbo.ContactView.ContactIDNum
>> WHERE (T.[Date] =
>> (SELECT MAX([Date])
>> FROM Meeting
>> WHERE ContactIDNum = T.ContactIDNum))
> try
> SELECT T.ContactIDNum, dbo.ContactView.Organization, dbo.ContactView.Name,
> dbo.ContactView.UPEAPM, T.UPEAContact, T.[Date]
> FROM dbo.MeetingView T
> RIGHT JOIN dbo.ContactView ON T.ContactIDNum =
> dbo.ContactView.ContactIDNum
> WHERE (T.[Date] IS NULL
> OR T.[Date] =
> (SELECT MAX([Date])
> FROM Meeting
> WHERE ContactIDNum = T.ContactIDNum))|||On Wed, 25 Aug 2004 18:55:46 -0400, Karl A. Homburg wrote:

>This one almost works. The only problem is that the ClientIDNum for the
>rows that do not have any meetings shows up as null.

Hi Karl,

Do the suggestions of Erland and me "almost work" as well?

Displaying the ClientIDNum for the rows without any meeting can be
achieved by replacing T.ContactIDNum (in the SELECT list) with
dbo.ContactView.ContactIDNum.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Query Help

What I want to generate is a list of all users and if they belong to a
site or not. Below is what I have for tables, sample data and the
results. I am a newb with writing my own SQL and could use a few
pointers and help.

For example if I have the following data:

Table: [Users]
UserIDUserName
1Bob
2Joe
3Sam

Table: [Users_Sites]
UserIDSiteID
12
32

Table: [Sites]
SiteIDSiteName
1Flower
2Spring
3Bush

If I pick Site 1 I get the list:

UserIDSiteID
<null>1
<null>1
<null>1

If I pick Site 2 I get the list:

UserIDSiteID
12
<null>2
32

Thanks in advance...Will (wrhighfield@.hotmail.com) writes:
> What I want to generate is a list of all users and if they belong to a
> site or not. Below is what I have for tables, sample data and the
> results. I am a newb with writing my own SQL and could use a few
> pointers and help.

SELECT us.SiteID, u.UserID
FROM Users_Sites us
LEFT JOIN Users u ON us.UserID = u.UserID
WHERE us.SiteID = @.siteid

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Monday, March 26, 2012

Query Hangs when Parameters are scaled down.

query from hell

if when the data is in mdb format the below query works

SELECT *
FROM [rating & px Tgt History]
WHERE ((([from] Like "*Init*" And [action] Like "*Target*")=False and deleted=false));

but when the access linked to backend is sql server via odbc i get this

ODBC call failed
{microsoft][odbc sql server driver][sql server]line 1:incorrect syntax near '=' #170

One thing that jumps out at me is the asterisk * needs to be a %... ex: Like '%Init%' ... the only other thing that looks wrong is you are using double quotes.. try using single quotes.

query from different sql servers

how can i get data from different sqlservers in tsql.
is there anyway different from below
Select * from opendatasource('SQLOLEDB','Data Source=...;User
ID=...;Password=...').dbname.dbo.tblnameSabri,
From the SQL BOL:
This example accesses data from a table on another instance of SQL Server.
SELECT *
FROM OPENDATASOURCE(
'SQLOLEDB',
'Data Source=ServerName;User ID=MyUID;Password=MyPass'
).Northwind.dbo.Categories
HTHJerry"Sabri AKIN" <SabriAKIN@.discussions.microsoft.com> wrote in message
news:29DFD275-8D2B-438C-8BFF-9C9B743EFC76@.microsoft.com...
> how can i get data from different sqlservers in tsql.
> is there anyway different from below
> Select * from opendatasource('SQLOLEDB','Data Source=...;User
> ID=...;Password=...').dbname.dbo.tblname
>

Monday, March 12, 2012

Query doesn't hangs when run using parameters

I have a weird problem in Sql Server 2005 that I need some help on. Below
you'll find two queries. Both queries are exactly the same; they query the
same database, query for the same info, same sql statement, same where
clause... The first query (the one with all the parameters included in the
sql statement) runs perfectly against my database in less than a second. The
second query is as I've said exactly the same, the only difference is that
the parameters are now passed using the SqlCommand.Parameters-collection. The
problem with this query is that it seems to cause Sql Server to hang. It
seems to execute my query (CPU varies between 70% and 100%) but it never
finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
this behavior? Is there something that I can do about it? For what it's
worth, my database hardly contains any data. Since it never seems to finish,
I'm not able to generate a execute plan for it to see what the problem is.
The good query:
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN (
SELECT a.[ID]
FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
OR
(
(
b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
b.[Keywords] LIKE 'Dit %' ESCAPE '~'
)
AND
(
c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
c.[Keywords] LIKE 'de %' ESCAPE '~'
)
AND
(
d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
)
AND
(
e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
e.[Keywords] LIKE 'eerste %' ESCAPE '~'
)
AND
(
f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
AND
f.[Keywords] LIKE 'optie %' ESCAPE '~'
)
)
) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
)
)
The bad query:
exec sp_executesql N'
SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
a.[ModifiedBy], a.[Tag], a.[FileCount]
FROM tblRECORDS a
WHERE a.[ID] IN
(
SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
ON a.[Id] = f.[RecordId]
WHERE
(
(
(
(
b.[LanguageId] = @.__0
AND
b.[Keywords] LIKE @.__1 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__2
AND
c.[Keywords] LIKE @.__3 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__4
AND
d.[Keywords] LIKE @.__5 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__6
AND
e.[Keywords] LIKE @.__7 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__8
AND
f.[Keywords] LIKE @.__9 ESCAPE ''~''
)
)
OR
(
(
b.[LanguageId] = @.__10
AND
b.[Keywords] LIKE @.__11 ESCAPE ''~''
)
AND
(
c.[LanguageId] = @.__12
AND
c.[Keywords] LIKE @.__13 ESCAPE ''~''
)
AND
(
d.[LanguageId] = @.__14
AND
d.[Keywords] LIKE @.__15 ESCAPE ''~''
)
AND
(
e.[LanguageId] = @.__16
AND
e.[Keywords] LIKE @.__17 ESCAPE ''~''
)
AND
(
f.[LanguageId] = @.__18
AND
f.[Keywords] LIKE @.__19 ESCAPE ''~''
)
)
)
AND
a.[ID] = @.__20
)
)',
N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
nvarchar(4),
@.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
nvarchar(8),
@.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
nvarchar(5),
@.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
nvarchar(13),
@.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
nvarchar(7),
@.__20
uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
@.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
@.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
@.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
@.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
@.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
@.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
@.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
@.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
@.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
@.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'Michael Vanhoutte wrote:
> I have a weird problem in Sql Server 2005 that I need some help on. Below
> you'll find two queries. Both queries are exactly the same; they query the
> same database, query for the same info, same sql statement, same where
> clause... The first query (the one with all the parameters included in the
> sql statement) runs perfectly against my database in less than a second. The
> second query is as I've said exactly the same, the only difference is that
> the parameters are now passed using the SqlCommand.Parameters-collection. The
> problem with this query is that it seems to cause Sql Server to hang. It
> seems to execute my query (CPU varies between 70% and 100%) but it never
> finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
> this behavior? Is there something that I can do about it? For what it's
> worth, my database hardly contains any data. Since it never seems to finish,
> I'm not able to generate a execute plan for it to see what the problem is.
> The good query:
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
> a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN (
> SELECT a.[ID]
> FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> OR
> (
> (
> b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> )
> AND
> (
> c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> c.[Keywords] LIKE 'de %' ESCAPE '~'
> )
> AND
> (
> d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> )
> AND
> (
> e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> )
> AND
> (
> f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> AND
> f.[Keywords] LIKE 'optie %' ESCAPE '~'
> )
> )
> ) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> )
> )
> The bad query:
> exec sp_executesql N'
> SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
> a.[ModifiedBy], a.[Tag], a.[FileCount]
> FROM tblRECORDS a
> WHERE a.[ID] IN
> (
> SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> ON a.[Id] = f.[RecordId]
> WHERE
> (
> (
> (
> (
> b.[LanguageId] = @.__0
> AND
> b.[Keywords] LIKE @.__1 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__2
> AND
> c.[Keywords] LIKE @.__3 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__4
> AND
> d.[Keywords] LIKE @.__5 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__6
> AND
> e.[Keywords] LIKE @.__7 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__8
> AND
> f.[Keywords] LIKE @.__9 ESCAPE ''~''
> )
> )
> OR
> (
> (
> b.[LanguageId] = @.__10
> AND
> b.[Keywords] LIKE @.__11 ESCAPE ''~''
> )
> AND
> (
> c.[LanguageId] = @.__12
> AND
> c.[Keywords] LIKE @.__13 ESCAPE ''~''
> )
> AND
> (
> d.[LanguageId] = @.__14
> AND
> d.[Keywords] LIKE @.__15 ESCAPE ''~''
> )
> AND
> (
> e.[LanguageId] = @.__16
> AND
> e.[Keywords] LIKE @.__17 ESCAPE ''~''
> )
> AND
> (
> f.[LanguageId] = @.__18
> AND
> f.[Keywords] LIKE @.__19 ESCAPE ''~''
> )
> )
> )
> AND
> a.[ID] = @.__20
> )
> )',
> N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
> nvarchar(4),
> @.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
> nvarchar(8),
> @.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
> nvarchar(5),
> @.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
> nvarchar(13),
> @.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
> nvarchar(7),
> @.__20
> uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
> @.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
> @.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
> @.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
> @.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
> @.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
> @.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
> @.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
> @.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
> @.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
> @.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'
Do a Google search for "parameter sniffing"
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Tracy,
Thanks for your reply. First of all, sorry for the brilliant typo in my
subject. I only noticed that now. ;-)
I see where you're going with your answer. I know that Sql Server caches the
execution plan of a query and that you might sometimes get a slower response
if it happens to use an execution plan that is not optimal for your
parameters.
However, I wonder if there's not something more going on here. The reason
why I'm saying this is that my table hardly contains any data. The biggest
table involved in my query contains a few dozen records. I mean that
literally: 61 records to be exact. There are a few other tables involved in
the view but they contain even fewer records. Most less than 10. So, even if
the execution plan would be completely wrong and Sql Server would use table
scans for everything, it should still finish instantaneously. Could the
impact really be this bad that it takes +20 minutes to query and join a few
tables that all contain less than 100 rows?
By the way, I have tried using RECOMPILE and the query does seem to run at
normal speed now.
Regards,
Michael
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > I have a weird problem in Sql Server 2005 that I need some help on. Below
> > you'll find two queries. Both queries are exactly the same; they query the
> > same database, query for the same info, same sql statement, same where
> > clause... The first query (the one with all the parameters included in the
> > sql statement) runs perfectly against my database in less than a second. The
> > second query is as I've said exactly the same, the only difference is that
> > the parameters are now passed using the SqlCommand.Parameters-collection. The
> > problem with this query is that it seems to cause Sql Server to hang. It
> > seems to execute my query (CPU varies between 70% and 100%) but it never
> > finishes. Last time I cancelled it after +20 minutes. Does anybody why I see
> > this behavior? Is there something that I can do about it? For what it's
> > worth, my database hardly contains any data. Since it never seems to finish,
> > I'm not able to generate a execute plan for it to see what the problem is.
> >
> > The good query:
> > SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn], a.[ModifiedBy],
> > a.[Tag], a.[FileCount]
> > FROM tblRECORDS a
> > WHERE a.[ID] IN (
> > SELECT a.[ID]
> > FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> > ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> > ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> > ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> > ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> > ON a.[Id] = f.[RecordId]
> > WHERE
> > (
> > (
> > (
> > (
> > b.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> > )
> > AND
> > (
> > c.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > c.[Keywords] LIKE 'de %' ESCAPE '~'
> > )
> > AND
> > (
> > d.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> > )
> > AND
> > (
> > e.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> > )
> > AND
> > (
> > f.[LanguageId] = 'c2bd4f9b-bb95-4bcb-80c3-1e924c9c26dc'
> > AND
> > f.[Keywords] LIKE 'optie %' ESCAPE '~'
> > )
> > )
> > OR
> > (
> > (
> > b.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > b.[Keywords] LIKE 'Dit %' ESCAPE '~'
> > )
> > AND
> > (
> > c.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > c.[Keywords] LIKE 'de %' ESCAPE '~'
> > )
> > AND
> > (
> > d.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > d.[Keywords] LIKE 'nederlandse %' ESCAPE '~'
> > )
> > AND
> > (
> > e.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > e.[Keywords] LIKE 'eerste %' ESCAPE '~'
> > )
> > AND
> > (
> > f.[LanguageId] = '00000000-0000-0000-0000-000000000000'
> > AND
> > f.[Keywords] LIKE 'optie %' ESCAPE '~'
> > )
> > )
> > ) AND a.[ID] = 'a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> > )
> > )
> >
> > The bad query:
> > exec sp_executesql N'
> > SELECT a.[ID], a.[CreatedOn], a.[CreatedBy], a.[ModifiedOn],
> > a.[ModifiedBy], a.[Tag], a.[FileCount]
> > FROM tblRECORDS a
> > WHERE a.[ID] IN
> > (
> > SELECT a.[ID] FROM tblRECORDS a INNER JOIN getRECORDFULLTEXT b
> > ON a.[Id] = b.[RecordId] INNER JOIN getRECORDFULLTEXT c
> > ON a.[Id] = c.[RecordId] INNER JOIN getRECORDFULLTEXT d
> > ON a.[Id] = d.[RecordId] INNER JOIN getRECORDFULLTEXT e
> > ON a.[Id] = e.[RecordId] INNER JOIN getRECORDFULLTEXT f
> > ON a.[Id] = f.[RecordId]
> > WHERE
> > (
> > (
> > (
> > (
> > b.[LanguageId] = @.__0
> > AND
> > b.[Keywords] LIKE @.__1 ESCAPE ''~''
> > )
> > AND
> > (
> > c.[LanguageId] = @.__2
> > AND
> > c.[Keywords] LIKE @.__3 ESCAPE ''~''
> > )
> > AND
> > (
> > d.[LanguageId] = @.__4
> > AND
> > d.[Keywords] LIKE @.__5 ESCAPE ''~''
> > )
> > AND
> > (
> > e.[LanguageId] = @.__6
> > AND
> > e.[Keywords] LIKE @.__7 ESCAPE ''~''
> > )
> > AND
> > (
> > f.[LanguageId] = @.__8
> > AND
> > f.[Keywords] LIKE @.__9 ESCAPE ''~''
> > )
> > )
> > OR
> > (
> > (
> > b.[LanguageId] = @.__10
> > AND
> > b.[Keywords] LIKE @.__11 ESCAPE ''~''
> > )
> > AND
> > (
> > c.[LanguageId] = @.__12
> > AND
> > c.[Keywords] LIKE @.__13 ESCAPE ''~''
> > )
> > AND
> > (
> > d.[LanguageId] = @.__14
> > AND
> > d.[Keywords] LIKE @.__15 ESCAPE ''~''
> > )
> > AND
> > (
> > e.[LanguageId] = @.__16
> > AND
> > e.[Keywords] LIKE @.__17 ESCAPE ''~''
> > )
> > AND
> > (
> > f.[LanguageId] = @.__18
> > AND
> > f.[Keywords] LIKE @.__19 ESCAPE ''~''
> > )
> > )
> > )
> > AND
> > a.[ID] = @.__20
> > )
> > )',
> > N'@.__0 uniqueidentifier,@.__1 nvarchar(5),@.__2 uniqueidentifier,@.__3
> > nvarchar(4),
> > @.__4 uniqueidentifier,@.__5 nvarchar(13),@.__6 uniqueidentifier,@.__7
> > nvarchar(8),
> > @.__8 uniqueidentifier,@.__9 nvarchar(7),@.__10 uniqueidentifier,@.__11
> > nvarchar(5),
> > @.__12 uniqueidentifier,@.__13 nvarchar(4),@.__14 uniqueidentifier,@.__15
> > nvarchar(13),
> > @.__16 uniqueidentifier,@.__17 nvarchar(8),@.__18 uniqueidentifier,@.__19
> > nvarchar(7),
> > @.__20
> > uniqueidentifier',@.__0='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__1=N'Dit %',
> > @.__2='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__3=N'de %',
> > @.__4='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__5=N'nederlandse %',
> > @.__6='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__7=N'eerste %',
> > @.__8='C2BD4F9B-BB95-4BCB-80C3-1E924C9C26DC',@.__9=N'optie %',
> > @.__10='00000000-0000-0000-0000-000000000000',@.__11=N'Dit %',
> > @.__12='00000000-0000-0000-0000-000000000000',@.__13=N'de %',
> > @.__14='00000000-0000-0000-0000-000000000000',@.__15=N'nederlandse %',
> > @.__16='00000000-0000-0000-0000-000000000000',@.__17=N'eerste %',
> > @.__18='00000000-0000-0000-0000-000000000000',@.__19=N'optie %',
> > @.__20='a6ff3da9-6618-4f62-9a24-79aad5e755ca'
> Do a Google search for "parameter sniffing"
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Hi Tracy,
> Thanks for your reply. First of all, sorry for the brilliant typo in my
> subject. I only noticed that now. ;-)
> I see where you're going with your answer. I know that Sql Server caches the
> execution plan of a query and that you might sometimes get a slower response
> if it happens to use an execution plan that is not optimal for your
> parameters.
> However, I wonder if there's not something more going on here. The reason
> why I'm saying this is that my table hardly contains any data. The biggest
> table involved in my query contains a few dozen records. I mean that
> literally: 61 records to be exact. There are a few other tables involved in
> the view but they contain even fewer records. Most less than 10. So, even if
> the execution plan would be completely wrong and Sql Server would use table
> scans for everything, it should still finish instantaneously. Could the
> impact really be this bad that it takes +20 minutes to query and join a few
> tables that all contain less than 100 rows?
> By the way, I have tried using RECOMPILE and the query does seem to run at
> normal speed now.
20 minutes does seem excessive for such little data, but it's still not
impossible to believe. Worst case, as you said, is that the execution
plan is so bad that it falls down to doing full table scans on all the
involved tables. That makes you vulnerable to a couple of things:
1. Blocking - you don't say how busy these tables are, so this is
speculation. Assuming that other processes are using these tables, your
table scans could be blocked. Even SELECTs can place locks on a table,
and those locks will interfere with a scan.
2. Resource contention - without knowing your hardware specs, this is
also speculation. If the server is extremely tight on memory, these
tables might have to be read from disk when the scan occurs. If your
drives are I/O bound, these reads will be affected.
There could be something other than parameter sniffing at work here, but
based on the symptoms, I'm going to stick with that as my answer.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Today, I wanted to update my code to include the RECOMPILE-option in the
query but the first time I executed the query with the RECOMPILE-option on, I
noticed that it again took forever to run. I have let it run for over two and
a half hours now and it is still busy, so I'm guessing that something else is
going on.
Some more info:
1. The query runs against the Sql Server on my laptop. I have plenty of
memory available when the query starts and my Sql Server is not used at all
except to run this query. No other users are connected.
2. Before the query starts, Sql Server is using about 150MB of RAM (of the
1,5GB that I have on my laptop) and after a couple of minutes, the memory
used by Sql Server increases up to more than 1GB. So, memory usage to run
this particular query is enormous. This is weird because as I've said it
queries tables that almost don't contain any records and it should return a
single record in the end.
3. I have checked in Activity Monitor while the query is running and there
are no blocking or blocked processes.
4. Also, the Sql Server process is constantly using CPU, with an average of
about 25% during the last two hours.
5. I have constant disc activity while the query is running
6. My laptop is simply not usable anymore while the query is running. Sql
Server is using so much memory and disc activity that anything I try to do
takes ages.
Any other insights on what might be causing this problem?
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Hi Tracy,
> >
> > Thanks for your reply. First of all, sorry for the brilliant typo in my
> > subject. I only noticed that now. ;-)
> >
> > I see where you're going with your answer. I know that Sql Server caches the
> > execution plan of a query and that you might sometimes get a slower response
> > if it happens to use an execution plan that is not optimal for your
> > parameters.
> >
> > However, I wonder if there's not something more going on here. The reason
> > why I'm saying this is that my table hardly contains any data. The biggest
> > table involved in my query contains a few dozen records. I mean that
> > literally: 61 records to be exact. There are a few other tables involved in
> > the view but they contain even fewer records. Most less than 10. So, even if
> > the execution plan would be completely wrong and Sql Server would use table
> > scans for everything, it should still finish instantaneously. Could the
> > impact really be this bad that it takes +20 minutes to query and join a few
> > tables that all contain less than 100 rows?
> >
> > By the way, I have tried using RECOMPILE and the query does seem to run at
> > normal speed now.
> 20 minutes does seem excessive for such little data, but it's still not
> impossible to believe. Worst case, as you said, is that the execution
> plan is so bad that it falls down to doing full table scans on all the
> involved tables. That makes you vulnerable to a couple of things:
> 1. Blocking - you don't say how busy these tables are, so this is
> speculation. Assuming that other processes are using these tables, your
> table scans could be blocked. Even SELECTs can place locks on a table,
> and those locks will interfere with a scan.
> 2. Resource contention - without knowing your hardware specs, this is
> also speculation. If the server is extremely tight on memory, these
> tables might have to be read from disk when the scan occurs. If your
> drives are I/O bound, these reads will be affected.
> There could be something other than parameter sniffing at work here, but
> based on the symptoms, I'm going to stick with that as my answer.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> Today, I wanted to update my code to include the RECOMPILE-option in the
> query but the first time I executed the query with the RECOMPILE-option on, I
> noticed that it again took forever to run. I have let it run for over two and
> a half hours now and it is still busy, so I'm guessing that something else is
> going on.
> Some more info:
> 1. The query runs against the Sql Server on my laptop. I have plenty of
> memory available when the query starts and my Sql Server is not used at all
> except to run this query. No other users are connected.
> 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> used by Sql Server increases up to more than 1GB. So, memory usage to run
> this particular query is enormous. This is weird because as I've said it
> queries tables that almost don't contain any records and it should return a
> single record in the end.
> 3. I have checked in Activity Monitor while the query is running and there
> are no blocking or blocked processes.
> 4. Also, the Sql Server process is constantly using CPU, with an average of
> about 25% during the last two hours.
> 5. I have constant disc activity while the query is running
> 6. My laptop is simply not usable anymore while the query is running. Sql
> Server is using so much memory and disc activity that anything I try to do
> takes ages.
> Any other insights on what might be causing this problem?
>
You should probably try to get a Profiler trace to see what SQL is doing
while the machine is getting hammered...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I don't believe it... I wanted to run a more detailed profiler trace today to
get some more information. I had already run a default trace earlier, but all
that got me was an RPC Starting-event with the Sql statement that was in my
report. Today, I activated ALL events in profiler and I started the query.
Guess what? I can't reproduce the problem anymore! Nothing has changed as far
as data, structure, queries... I'm guessing that Sql Server automatically
updated some statistics which causes it to use a different execution plan.
This morning, I said to myself to take a backup of the database while the
problem is still there, but apparently I'm too late. We'll see in the coming
days if the problem comes back. Thanks for your help so far!
"Tracy McKibben" wrote:
> Michael Vanhoutte wrote:
> > Today, I wanted to update my code to include the RECOMPILE-option in the
> > query but the first time I executed the query with the RECOMPILE-option on, I
> > noticed that it again took forever to run. I have let it run for over two and
> > a half hours now and it is still busy, so I'm guessing that something else is
> > going on.
> >
> > Some more info:
> > 1. The query runs against the Sql Server on my laptop. I have plenty of
> > memory available when the query starts and my Sql Server is not used at all
> > except to run this query. No other users are connected.
> > 2. Before the query starts, Sql Server is using about 150MB of RAM (of the
> > 1,5GB that I have on my laptop) and after a couple of minutes, the memory
> > used by Sql Server increases up to more than 1GB. So, memory usage to run
> > this particular query is enormous. This is weird because as I've said it
> > queries tables that almost don't contain any records and it should return a
> > single record in the end.
> > 3. I have checked in Activity Monitor while the query is running and there
> > are no blocking or blocked processes.
> > 4. Also, the Sql Server process is constantly using CPU, with an average of
> > about 25% during the last two hours.
> > 5. I have constant disc activity while the query is running
> > 6. My laptop is simply not usable anymore while the query is running. Sql
> > Server is using so much memory and disc activity that anything I try to do
> > takes ages.
> >
> > Any other insights on what might be causing this problem?
> >
> You should probably try to get a Profiler trace to see what SQL is doing
> while the machine is getting hammered...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Michael Vanhoutte wrote:
> I don't believe it... I wanted to run a more detailed profiler trace today to
> get some more information. I had already run a default trace earlier, but all
> that got me was an RPC Starting-event with the Sql statement that was in my
> report. Today, I activated ALL events in profiler and I started the query.
> Guess what? I can't reproduce the problem anymore! Nothing has changed as far
> as data, structure, queries... I'm guessing that Sql Server automatically
> updated some statistics which causes it to use a different execution plan.
> This morning, I said to myself to take a backup of the database while the
> problem is still there, but apparently I'm too late. We'll see in the coming
> days if the problem comes back. Thanks for your help so far!
>
If you have auto-stats enabled, or run a scheduled stats update, that
very well could be what happened. Or, it could have been an inefficient
query plan that was cached, and has since dropped out of the cache.
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Wednesday, March 28, 2012

Query Help

Query Help

Monday, March 26, 2012

Query Hangs when Parameters are scaled down.

query from hell

query from different sql servers

Monday, March 12, 2012

Query doesn't hangs when run using parameters

query assistance

Blog Archive

About Me