SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


General View Performance Question


General View Performance Question

Author
Message
Davebhoy
Davebhoy
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 44
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86764 Visits: 41103
Davebhoy (7/4/2013)
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.


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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Davebhoy
Davebhoy
Valued Member
Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)Valued Member (72 reputation)

Group: General Forum Members
Points: 72 Visits: 44
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)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86764 Visits: 41103
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Sean Pearce
Sean Pearce
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1784 Visits: 3432
Davebhoy (7/4/2013)
Here's the SQL which runs instantly but if I change @client to 'XX' then it takes 20 seconds.

Please supply the actual execution plans for both.



The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search