Post reply

Table Join kills Performance

  • February 24, 2005 at 3:55 am

    #113458

    I can't understand why my table join on this one particular page is making everything so slow. I select three fields from the main table, and then one field from each of the two joined tables.

    Is there any particular order in which I select the fields of each table that affects performance? If I run the query in Microsoft access it runs instantly. But if I run it in asp code it takes forever.

    I have used mutiple table joins in many other places and everything is lightning quick.

     

    Kindest Regards,

    Jon
    Free Cell Phone Games

  • February 24, 2005 at 11:57 pm

    #544070

    You have to be more specific:

    - how many records do you have in each table

    - what type are the join-attributes of

    - do you have indexes on the join-attributes

    - what says the execution plan

  • February 25, 2005 at 12:09 am

    #544072

    Well for starters I don't think I am doing this in the best possible way for performance.

    For some resaon, now the exact same code runs lickety split.

    I know that I definitely should be opening only snapshots unless I need to update the data. That should speed the whole thing up a bit.

    I hear stored procedures are the way to go to optimize performance, but the syntax looks so greek to me. I am so used to writing SQL queries and replacing criteria with form fields.

    How do I pass criteria from a form field to a stored procedure to ge the right recordset?

     

     

    Kindest Regards,

    Jon
    Free Cell Phone Games

  • February 25, 2005 at 1:39 am

    #544084

    Best guess is lack of indexes on the join columns and now SQL has created statistical indexes for these and the joins are now using them. 

  • February 25, 2005 at 3:21 am

    #544102

    you may want to post some sql and ddl (including indexes) and we may be able to help.

    Like athurgar replied most likely absense of indexes may cause the issue !

     

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • February 25, 2005 at 5:34 am

    #544123

    I have 2 guesses

    - May be the connection is eating time.

    - The final query is missing join conditions.

    Please review the asp code again for these two places.

    OR

    Send on the segment of your asp code where you are making connection and sending sql query.

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply