#1
  1. No Profile Picture
    Registered User
    ASP Explorer (0 - 99 posts)

    Join Date
    Oct 2007
    Posts
    1
    Rep Power
    0

    Performance Issue using left or substring function


    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','90 2','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 containt approx. 6000 row and Table2 containt only 210 rows

    The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt 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 secondes.

    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','90 2','905')))
    the query take an execution time of ~15 secondes!!!!

    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?
  2. #2
  3. Unholy Moderator
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Location
    In hell, where did you think?
    Posts
    12,026
    Rep Power
    659
    Why are you using a sub-select? Why not just use an inner join?

    Code:
    SELECT A.*
    FROM Table1 As A
    INNER JOIN
    (
       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','90 2','905'))
    ) As B On (A.DrID = B.DrID)

Similar Threads

  1. Calling VBScript function via onclick event
    By bluh_11 in forum ASP Development
    Replies: 5
    Last Post: August 16th, 2006, 06:18 PM
  2. Stuck on a small issue
    By sam_sam in forum ASP Development
    Replies: 6
    Last Post: March 29th, 2005, 11:46 AM
  3. Date function on database fields
    By Lexy in forum Visual Basic Programming
    Replies: 1
    Last Post: March 22nd, 2005, 12:01 PM
  4. Problems with parsing variables to Function
    By Monkey in forum ASP Development
    Replies: 2
    Last Post: November 13th, 2003, 09:22 AM
  5. Performance Issue
    By Steve Schofield in forum ASP Development
    Replies: 0
    Last Post: January 31st, 2000, 11:52 PM

IMN logo majestic logo threadwatch logo seochat tools logo