Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JAdauto
Posting Yak Master
160 Posts |
 Posted - 2010-04-20 : 23:51:52
|
| I have the following query which seems simple enough, but it takes 58 seconds to run each time I process when there is an incredibly large database (22K + records). Incredible or not, 58 seconds seems like way too long (which abot 10K get returned in this query). Other then the large number of records, there are a ton of these IsNull's in this Select statement. I was wondering if this function is a time hog or if someone sees ways to optimize this better.Thank you,JAdautoSELECT Entity.*, (IsNull(LastName,'') + IsNull(RTRIM(' ' + SuffixDesc), '') + IsNull(', ' + FirstName,'') + IsNull(' ' + MiddleInitial, '')) as MemberName, (IsNull(Number,'') + IsNull('-' + NumberSuffix,'')) as MemberNumber, (IsNull(LastName,'') + IsNull(', ' + FirstName,'') + IsNull(' ' + MiddleInitial, '') + '(' + Number + '-' + NumberSuffix + ')') as MemberNameNumber, TypeID as MemberTypeID FROM Entity WHERE Entity.EntitySubTypeENUM = 2 AND Entity.RelationshipENUM = 1 ORDER BY MemberName |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-04-21 : 01:26:08
|
| can i ask what you're trying to achieve? could you explain it with sample data and output out of it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 02:02:46
|
| Take the ISNULLs out and see if it runs any faster? (I doubt it though ...). you could also try replacing them with COALESCE() in case that is faster (I doubt that too!)10K rows returned to the Client can take a significant time in itself, and the WHERE clause looks like it will use a Table Scan (a guess, but slightly educated guess I hope!) which won't be fast (and probably cannot be speeded up with indexes etc.)You should never use "SELECT *" in queries, but rather list the columns that you need. Someone might add several massive "Notes" type columns to the Entity table in the future (or there might be some there now, which the Client application does not need / use) and they will dramatically slow down everything - SQL will waste cache on them, and thus churn its memory more quickly, more disk reads, more network traffic, more CPU and Memory for the Client app to read the ResultSet and so on ... plus given that you are selecting all columns why not have the Client App do the concatenation? |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2010-04-21 : 02:18:39
|
How about index ?Do you have any index on EntitySubTypeENUM or RelationshipENUM ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-04-21 : 02:20:33
|
| Those ENUM columns don't look very selective though? |
|
|
|
|
|
|
|
|
Is IsNull a time hog?