Basic query performance

Question

Basic query performance

January 11, 2024 at 1:19 pm

This was removed by the editor as SPAM
January 16, 2024 at 5:33 am

This was removed by the editor as SPAM

Viewing 10 posts - 16 through 24 (of 24 total)

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

Jonathan AC Roberts SSCoach Points: 18363 More actions · Answer 1

krypto69 wrote:

Thanks Jonathon.
Thanks Brian - yeah that table gets massive inserts and updates so I guess indexed view is a bad option.
Tried using exists - same run time:
select distinct
[e].[eligibility_id]
from
[dbo].[eligibility] as [e]
WHERE EXISTS (select member_id from [MemberPhoneLookup] as [mpl] where [mpl].[member_id] = [e].[member_id])

What indexes do you have on the tables?

An index on: MemberPhoneLookup(member_id) would help and also one on: dbo.eligibility(member_id) INCLUDE (eligibility_id)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

ALSO I am not saying "don't use indexed views", I am saying test it. Even just adding 1 index will impact INSERT, UPDATE, and DELETE performance (and can impact SELECT performance too... Pinal Dave has a good video on that). The impact to DML operations MAY be light enough that an indexed view is a good route. The performance benefit may be worth the impact to DML.

I am still curious on how the performance tanked though. Was it gradual over time or was it good yesterday and slow today? Also what changed between the "quick" time to today? Just want to make sure we are addressing the correct issue. If you (for example) upgraded to 2014 or newer, turning on the legacy cardinality estimator may help. What tool is running this query (SSIS, SSMS, stored procedure, etc) and where is the result of that query going? If it is SSMS, then I'd turn on statistics IO and statistics time as your slowness is VERY VERY likely due to presenting to screen and/or downloading to SSMS. If it is SSIS, do you have enough free RAM to run the package? Are you able to view the actual execution plan so you can review what is going on? Are there any weird results like estimating 1 row but getting 22 million? If so, statistics are likely out of date.

Tuning the query with ONLY seeing the query results in a lot of suggestions that MAY or MAY NOT be good. For example, adding an index when an identical one already exists won't help performance at all but will hurt DML performance.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

krypto69 SSChampion Points: 13604 More actions · Answer 3

Sure, logical assumption and my fault for not specifying

It returned in same time..

krypto69 SSChampion Points: 13604 More actions · Answer 4

I think I just may be stuck with "it is what it is" - I know its a ton of records. Really just wanted to make sure I tried everything before I gave up and did some nonsense move like move all the data at night to a load type table for use only for this process. Then I could apply an indexed view to that dedicated table. But just hate doing that hokey kinda of stuff...duplicate data, etc. Just feels wrong. Hoping there was another magical method I wasn't thinking about.

This reply was modified 11 months, 3 weeks ago by krypto69.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 5

If it is "results to screen", it is more likely the rendering on screen that is the slowness and not the data retrieval/download process and in that case, an indexed view will not offer the performance boost you think it will. You could confirm this by dumping it to a file instead of to screen from SSMS. If this isn't from SSMS, knowing which tool is being used will help determine the best course of action.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Emperor100 SSCrazy Points: 2173 More actions · Answer 6

Mr. Brian Gale wrote:

You could confirm this by dumping it to a file instead of to screen from SSMS. If this isn't from SSMS, knowing which tool is being used will help determine the best course of action.

If I look at one of the OP's response, Universe was mentioned. I am assuming it is SAP Business Objects Universe /Information Design Tool. This tool doesn't return the results to the screen as it is a middle layer designer tool.

I may be wrong...

=======================================================================

none SSC Rookie Points: 29 More actions · Answer 7

First I would create a clustered index on the [memberphonelookup] table

Then create a non-clustered index on the [eligibility] table with an include statement for the field(s) you want. The reason you need an include is that otherwise, the optimiser might ignore the index if it can't work out a sensible execution plan.

I would avoid using views even with schema binding and an index on the view. Instead, I would create a temporary table with an index on it. Then use that temp table to interact with the rest of your code.

Please see the code example below:

-- Create table
CREATE TABLE [dbo].[memberphonelookup](
member_id INT
)

CREATE TABLE [dbo].[eligibility](
eligibility_id INT,
member_id INT
)

-- Create clustered index
CREATE CLUSTERED INDEX ix_memberphonelookup_member_id
ON [memberphonelookup] ([member_id]);

-- Create clusterd index with include
CREATE NONCLUSTERED INDEX ix_eligibility_member_id  
ON [eligibility] ([member_id])
INCLUDE (eligibility_id);  

-- Put results into memory and add index as this will be faster to interact with the rest of your code
CREATE TABLE #temp_eligibility (
    eligibility_id INT
);

INSERT INTO #temp_eligibility
SELECT [e].[eligibility_id]
FROM   [dbo].[eligibility] AS [e]
       INNER JOIN [memberphonelookup] AS [mpl]
               ON [mpl].[member_id] = [e].[member_id]

CREATE CLUSTERED INDEX ix_temp_eligibility_eligibility_id
ON #temp_eligibility ([eligibility_id]);

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 8

Just wanted to point out that rmeldrum indicated in their script that temp tables put the results in memory. This is not accurate. Temp tables are written to tempdb which resides on disk. Now, you CAN have tempdb in a RAMDISK which would be in memory then or you can have memory optimized temp tables which go in memory instead of disk.

My theory (I would want to test to confirm) is that the temp table isn't going to improve performance UNLESS you are using a linked server to pull some of the data.

IF the query is slow due to blocking, you may get a performance boost (at the risk of bad data) by adding a nolock hint or a readpast hint or setting the transaction isolation level to read committed snapshot isolation... but if blocking isn't the cause of the slowness and it is slow due to data volume, then these things may not matter.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.