T-SQL Query select with a heap table without join

Question

T-SQL Query select with a heap table without join

Esimbozel

SSC Enthusiast

Points: 113
More actions
January 5, 2021 at 1:33 pm

#3827557

Hi,
i have a query for selecting data on multi tables with a join but one of my tables is a heap table with million rows without join (no clustered index and no non_clustered index)
performance is very bad, do you have an idea to perform?

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

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

If you provide the following, there is a chance that you will get useful assistance. Without it, there's not much we can say.

DDL for the tables involved in the query
The query itself
The query's Actual Execution Plan (as an attachment)

frederico_fonseca SSCoach Points: 16251 More actions · Answer 2

add required indexes - and see why the table does not have any at all - including a clustered index as that is normally, although not always, a good idea

ScottPletcher SSC Guru Points: 101201 More actions · Answer 3

If you're joining to the table, and it currently has no indexes at all, then create a clustered index on the WHERE columns and/or JOIN column(s).

To give you anything more specific, as Phil Parkin as noted, we'd need to see DDL for the table and the query itself at the very least.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Mr. Brian Gale SSC-Insane Points: 24939 More actions · Answer 4

As an alternate thing about the performance (just a guess as we have no DDL or query itself), indexes MAY not help. You say that it is a table with million rows without JOIN. Since there is no JOIN, the indexes are only going to help if you are filtering some of the data or ordering the data. That is, if there is a WHERE clause or an ORDER BY clause.

If we assume (probably incorrectly, but it is my interpretation of your question that you have multiple SELECTs, one that does JOINs on tables and one that does so without a JOIN) that your query on that slow table is something like:

SELECT *
FROM LargeTable

and that's it, it is likely that the slowness is caused by:

A - blocking - if something else is writing to the table, you may need to wait for those writes to finish before you can read it (depending on isolation level).

B - data size - if you are pulling 1 million rows and each row is 1 KB in size, you are pulling 1,000,000 KB of data, or 1 GB. Pulling 1 GB of data will take time as you need to read it from disk and push it out across the network to the machines. Plus, that is likely not the only query being processed on the server at that time, so performance will be impacted by other things running on the server too.

My questions for you are:

1 - is my interpretation of your question correct or close (ie my qeury)?

2 - how bad is "very bad". Are you talking about pulling 1 million rows in 1 second as "bad" or 1 million rows per hour as "bad"?

3 - is the query ALWAYS slow or usually fast with the odd slow time?

4 - is there any blocking happening when the query is slow?

5 - Should there be indexes on that table?

6 - Are you chasing the correct "slow" part of your query? Are you certain that that million row heap is causing the slowness (likely, but doesn't hurt to confirm).

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.