Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

General View Performance Question Expand / Collapse
Author
Message
Posted Thursday, July 4, 2013 10:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:58 AM
Points: 18, 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.
Post #1470475
Posted Thursday, July 4, 2013 11:44 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470492
Posted Thursday, July 4, 2013 12:35 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 31, 2014 9:58 AM
Points: 18, 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)
Post #1470497
Posted Thursday, July 4, 2013 4:48 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:00 AM
Points: 37,099, Visits: 31,650
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470529
Posted Friday, July 5, 2013 4:06 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 6:37 AM
Points: 906, Visits: 2,863
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
Post #1470650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse