October 18, 2007 at 1:18 pm
Hi,
I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS.
select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey.
The Table1 contain approx. 6000 row and Table2 contain only 210 rows
The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 contain the field "PostalCode" and "Telephone".
When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 seconds.
When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1
like in the following query :
select * from Table1 where drid in (SELECT DrID FROM Table2
WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905')))
the query take an execution time of ~15 seconds!!!!
This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second.
It’s weird because, Sql Express 2005 is supposed to be more performant
than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!!
Anyone have an idea why?
October 18, 2007 at 1:30 pm
What are the indices on your tables? Are the databases on the same machine? How much memory is allocated to SQL Server 2005 vs. SQL Server Express?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 18, 2007 at 2:02 pm
1. Do you mean tables "Indexes". If you mean "indexes" instead of indices : for the Table1 it's DrID and in Table2 it's ResponseID which is not included in the query. The two table are link by DrID as foreign key in Table2
2. The Database are on the same machine.
3. The memory usage for each process is approximately the same ~45 mb
SqlExpress2005 46,076k
SqlServer2005 47,472k
This problem is really weird because this problem occurs only in Sql Server Express 2005.
The execution time is ~ the same in MACCESS and Sql Server 2005
I though maybe having Sql Express and Sql Server 2005 on the same machine cause some problem but
I've already tried to install the program on a fresh installation on winxp. The result are the same.
The problem only occurs when the substring or left function are included into the query.
October 18, 2007 at 2:25 pm
The indexes ain't going to matter much because the function in the where clause is going to cause a table scan.
Mathieu - you don't happen to have the AutoClose option turned on in the DB properties in SQLExpress? The database "opening" process can cause some funky delays). SQL Express seems to love turning that thing on....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
October 18, 2007 at 2:36 pm
Actually either indexes or indices can be used as the plural of index. I tend to use indices because no one else does.
Have you compared the execution plans between SQL Server Express and SQL Pro? YOu can have the graphical execution plan show by going to Query -> Include Actual Execution Plan.
You may have to update the statistics for the SQL Server Express DB in order to get the same performance. Also using a function on a column in a where or join typically will hinder performance of any query as it usually causes an table scan.
If you execute Set ShowPlan_XML On and send the results to a file you can attach each file to a response here and someone can look at them and make a suggestion.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 18, 2007 at 2:51 pm
The auto-close is turned-off. It's not a problem cause by opening or closing a database. The test have being done with the using the application itself and in Sql Server 2005 Management Studio and on two different machine(my machine and a machine with a clean installation) and still the same issue. The problem only occurs when I integrate the left function or the substring function in the query. I've tried a lot of solution but the result is always the same. I can't change the structures of the query. I changed the structures of the query for test purpose and everything work fine. But i can't change the query structure. Both server are configure to use from 0 to 2 gig of memory allocation. The CPU Usage for the Query in Sql Express 2005 is about 50 to 60 % while executing the query, instead of less than 5% during a half second for Sql Server 2005.
October 18, 2007 at 2:59 pm
No, i didn't compared the execution plans between SQL Server Express and SQL Pro but it's a pretty good idea.
Thank's for your help
October 18, 2007 at 3:24 pm
One way to speed that up is to put calculated fields in place which grab the left(...) and substring(...) functions. Index those and you should get very good behavior. Of course - you should be seeing the same bad behavior on both versions of SQL.
Also - Express will not use >1GB ram. It's one of the built-in limitations.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply