Table Join kills Performance
-
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, -
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
-
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, -
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.
-
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
-
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