General View Performance Question
-
Hi there
I'm a novice a at SQL so hopefully this is an easy answer for someone.
I have a basic view which queries two tables in separate databases with one join on 'client' which also contains a WHERE clause using 'client'.
When I use a parameter for this column called @client (=@client) the results return almost instantly but when I use the same parameter as a text value (='XX'). It takes 20 seconds to return he same results!
Any help is appreciated.
Dave.
-
Davebhoy (7/4/2013)
Hi thereI'm a novice a at SQL so hopefully this is an easy answer for someone.
I have a basic view which queries two tables in separate databases with one join on 'client' which also contains a WHERE clause using 'client'.
When I use a parameter for this column called @client (=@client) the results return almost instantly but when I use the same parameter as a text value (='XX'). It takes 20 seconds to return he same results!
Any help is appreciated.
Dave.
What is the datatype of the column in the underlying table that you're trying to compare to and what is the datatype of @Client? It might simply be a datatype mismatch causing a table scan instead of an index seek. Can't tell for sure because I don't have the schema for your tables, nor the code for your view, nor any information for what @Client actually is.
--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) -
Thanks for your reply Jeff.
The datatype char(2) in both tables and client is indexed on both tables. Here's the SQL which runs instantly but if I change @client to 'XX' then it takes 20 seconds.
SELECT 'OBL' AS valtype, 'CY' AS Year, NULL AS TT, trans.account, SUM(trans.amount) AS Euro, GETDATE() AS date_created,
users_clients.user_id
FROM agr.dbo.trans AS trans INNER JOIN
ccas.gen_users_clients AS users_clients ON trans.client = users_clients.client
WHERE (trans.period BETWEEN users_clients.afs_cy_obperiod AND users_clients.afs_pto) AND (users_clients.active = 1)
GROUP BY trans.account, users_clients.user_id, trans.client
HAVING (trans.account BETWEEN '21100' AND '21270') AND (users_clients.user_id = @user_id) AND (trans.client = @client)
-
I don't have your tables or data so I can't really tell what's going on especially since I can't gen the actual execution plan. I did do a simple test between a lookup for a CHAR(2) @Client and 'XX', which SQL Server considers to be a VARCHAR(2) and I didn't see any type of datatype-precedence gotcha there.
I guess that if we really want to find out, we're going to need some more information for the "performance problem". See the 2nd link in my signature line for how to do that.
Shifting gears a bit, I see some strange things in your query. You GROUP BY @Client, which I believe to be unnecessary because you're only looking for 1 Client to begin with. You aggregate everything and then return only the given Client using HAVING which usually happens after the GROUP BY is done. You also limit accounts in the HAVING instead of the initial critera in the WHERE clause. I don't know if that's a part of the original problem but I believe I'd change it, none the less. Like this...
SELECT ValType = 'OBL',Year = 'CY',
TT = NULL,
trans.account,
Euro = SUM(trans.amount),
date_created = GETDATE(),
users_clients.[user_id]
FROM agr.dbo.trans AS trans
JOIN ccas.gen_users_clients AS users_clients
ON trans.client = users_clients.client
WHERE users_clients.client = @client
AND trans.client = @client
AND users_clients.[User_ID] = @user_id
AND users_clients.active = 1
AND trans.account BETWEEN '21100' AND '21270'
AND trans.period BETWEEN users_clients.afs_cy_obperiod AND users_clients.afs_pto
GROUP BY trans.account, users_clients.[user_id]
;
--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)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply