Why Does A SQL Inner Join Bring Performance To A Crawl?

[iRadiate]

Thanks, but pulling in 13,000 records with the SQL query is not the issue. As stated, the query returns the result set in less than 1 second in SSMS. Whatever Alpha is doing with this query is the cause of the massive performance hit.
I have switched to using a view for the list data and this returns in less than 2 seconds which is more than adequate for my needs.

Thanks for the offers but I think this issue is put to bed

[Russell Craycraft]

When I need to pull in a lot of records, I will delay populating the list until after search. Then just throw a dropdown box that will filter the list down. I don't think it matters if you are only showing 100 items, the SQL statement is still pulling in 13,000 records with a TON of fields, and will continue to be slow. Also turn off get count of records in the first tab in list properties, this will prevent 2 queries, and only do 1.

madtowng I can help you with creating a search option or anything with a list.

I can help over Skype or Zoom if needed.

[peteconway]

I think this may the best post of the year.
Thanks - very interesting..
Pete

[DoronF]

Hi There,

Where ever you see and Index scan it isn't what you want to see in a query plan. You need to try to turn scan into Seek. which means retrieving the data by just using the index tree.
Make sure you have indexes for each Join. Include some of the fields in the Order within the index of the FK and see.
In addition you don't have any WHERE Clause , that may cause the Server to scan the entire clustered index to return all rows.

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com

Avis Car Rental Software
MS SQL Blog: http://www.dfarber.com/computer-consulting-blog/

[iRadiate]

SMMS Query Execution Plan (with the ORDER BY clause)

Query Execution Plan (with ORDER BY).jpg


SMMS Query Execution Plan (without the ORDER BY clause)

Query Execution Plan (without ORDER BY).jpg

[iRadiate]

The CASE WHEN with the LIKE could be a major problem and slow down performance.

This is ONLY the case when Alpha handles the query in the list builder. SSMS has absolutely no issue running the query with the CASE WHEN and LIKE clause very quickly, as demonstrated by the video links I posted.

Also for a List View un-check the this property: Get count of Records in query.
Otherwise that query will run twice to figure out the number of total records the data set created.

I do not return the record count.

[iRadiate]

Same query with JOIN but using the view instead of SQL query in the list builder ...

<2 seconds ... https://www.screencast.com/t/PUd3fYflR3


That's a heck of a lot better than >15 seconds for whatever Alpha is doing with the query.

[DoronF]

Hello There,

In order for you see how the query looks like I will use In MS SQL management studio the Execution Plan option found here:


MS-SQL-Execution-Plan.jpg

Adding index on the foreign key is MUST but does not necessarily resolve the problem all the time since you may need to include additional field in that index.

The CASE WHEN with the LIKE could be a major problem and slow down performance.

This is how an Execution Plan looks like after a query is running. Hoover on top of each item show where the problem is.

Also for a List View un-check the this property: Get count of Records in query.
Otherwise that query will run twice to figure out the number of total records the data set created.


MS-SQL-Actulal-Execution-Plan.jpg

Regards,

Doron
The Farber Consulting Group, Inc.

Main Web Site: http://www.dFarber.com

Avis Car Rental Software
MS SQL Blog: http://www.dfarber.com/computer-consulting-blog/

[iRadiate]

Your case statement should be in your view already, so you don't need it any longer in Alpha. However, clicking portable SQL will often help when trying to add SQL functions to select statements. (A view is still often a better choice though.)

vw_repairs is a fine name so long as you always want machines linked to your repairs view. Technically, if you ever need a view just over the repairs table ( like maybe you have a bunch of calculations you want to do ) that would be better for a view called just vw_repairs. So you could call it vw_repairs_machines instead.

you can also call it v_repairs or view_repairs or repairs_vw - really anything. Just so long as you pick something and try to stick with it so you are not looking all over trying to find the view you want and figure out what you were thinking when you wrote it!

Sometimes you might want to join together three or more tables, so then calling the view vw_table1_table2_table3 etc gets a bit crazy. So instead you might name the view by how it is used, like vw_repairs_for_billing

Yes, the case statement is in the view. It was just complaining when I tried to switch over from the original custom query to using the view, that's all.

I think I like vw_repairs_machines better so I know it is linked with the machines table.

Once again, thanks for all the help .. much appreciated! And, I learned something about views

[lgrupido]

Your case statement should be in your view already, so you don't need it any longer in Alpha. However, clicking portable SQL will often help when trying to add SQL functions to select statements. (A view is still often a better choice though.)

vw_repairs is a fine name so long as you always want machines linked to your repairs view. Technically, if you ever need a view just over the repairs table ( like maybe you have a bunch of calculations you want to do ) that would be better for a view called just vw_repairs. So you could call it vw_repairs_machines instead.

you can also call it v_repairs or view_repairs or repairs_vw - really anything. Just so long as you pick something and try to stick with it so you are not looking all over trying to find the view you want and figure out what you were thinking when you wrote it!

Sometimes you might want to join together three or more tables, so then calling the view vw_table1_table2_table3 etc gets a bit crazy. So instead you might name the view by how it is used, like vw_repairs_for_billing

[madtowng]

I usually put the word view in the name to be able to easily identify views while performing queries. It's obviously a personal choice,
and helps me when it's time to make changes or track down problem code.

[iRadiate]

One more quick question, this is the first time I have used a view. My original table is called 'repairs' and the linked table is called 'machines'

I called the view 'vw_repairs'

Does anyone have any preferences on naming convention for views?

[iRadiate]

Have you tried the view yet? That would take the join out of Alpha completely. (And you need to index the foreign key.)

I have tried the view this morning, and this works well. Returns searches in about 2-3 seconds.

I noticed something interesting though, when I tried to change the list data source from my current sql query to the new view, it complains that it can't change the source because my source query is too complex. It points to the CASE statement as the culprit.
Maybe that was the whole issue to start with, though it runs just fine in the list until I tried to add the join.

SQL Soutce.jpg

I just deleted the custom query then chose the view instead, but I wonder why it complains about the CASE statement and if that was causing the issues.


I'm going to keep playing around with this since it definitely seems to be the way to go in terms of performance.
Thanks to all for all the great ideas.

[iRadiate]

So three things:
1) If you go to the query definition in Alpha and click on Native SQL you can see if there's something weird about the query being run in SQL Server.
2) the demo video in the app is using a where clause and the demo in SSMS isn't. Is the query still lightning fast in SSMS when you filter by the criteria you're showing in the Alpha video?
3) I've found that LEFT OUTER JOIN can perform a lot faster than INNER JOIN at times, so if it will produce the same records, you could try that.

1) I don't see anything different than what the query syntax shows in my first post
2) That's true, I never thought about it but you're right, the list filter actions has to introduce a where clause. But, as this shows, it makes no difference to the speed that SSMS returns the result ... https://www.screencast.com/t/AK96IGFyi
3) I just tried a FULL OUTER JOIN instead but it is still fast in SSMS and slow in Alpha. I'm still left with why it is okay in SSMS but not in Alpha.

[iRadiate]

Have you tried the view yet? That would take the join out of Alpha completely. (And you need to index the foreign key.)

If you would like, I could hop on a teamviewer session with you to take a look.

If you want to do that, private message me and I'll give you my direct email/phone.

Sorry, no Larry I haven't had time yet, been putting out fires at my real day job