Multi tool use

Clash Royale CLAN TAG

Why SQL query takes too long time to execute when fetching bulk data (without space)?

I have a problem, when getting bulk amount of data from a table.

I have a database table TblJobs, in this table some columns contain bulk amount of data (approx 60,000 characters in this column).

TblJobs

My table:

TblJobs

JobId JobTitle JobDescription
----------------------------------------------------------------
1 Job1 TextTextTextTextTextTextTextTextTextTextTextText... (approx 40,000 characters without any space in job description)
2 Job2 HelloHelloHelloHelloHelloHelloHelloHelloHelloHell..(approx 60,000 characters without any space in job description)
3 Job3 DemoDemoDemoDemoDemoDemoDemoDemoDemoDemoDemoDemo...(approx 60,000 characters without any space in job description)
4 Job4 TestingTestingTestingTestingTestingTestingTesti....(approx 50,000 characters without any space in job description)


Structure of table is:

JobId - Int
JobTitle - VarChar(500)
JobDescription - VarChar(MAX)


Now my problem is, when I execute query to select all columns from TblJobs it takes too long to execute (approx 30 sec). using this -

TblJobs

Select * from TblJobs


or

Select JobId, JobTitle, JobDescription from TblJobs


I was surprised when modify some data into table's column JobDescription, this query execute in 3-5 sec only.

JobDescription

In modification - I provide some spaces between data of JobDescription column.

JobDescription

For example you can see following table, in this I only include some space between jobDescription column (I didn't change datatype or amount of data):

jobDescription

JobId JobTitle JobDescription
------------------------------------------------------------------------
1 Job1 Text TextTextText**<space>**TextTextTextText**<space>**TextTextTextText... (approx 40,000 characters with some space in job description)
2 Job2 HelloHello**<space>**HelloHelloHelloHello**<space>**HelloHelloHelloHell..(approx 60,000 characters with some space in job description)
3 Job3 DemoDemoDemoDemo**<space>**DemoDemoDemoDemoDemo**<space>**DemoDemoDemo...(approx 60,000 characters with some space in job description)
4 Job4 TestingTesting**<space>**TestingTestingTesting**<space>**TestingTesti....(approx 50,000 characters with some space in job description)


So my question is, why select query takes too long time to execute when jobdescription don't have any space?
I think, time issue not related to amount of data in my case.

jobdescription

This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.

3 Answers
3

I don't really know why it likes this, but a couple of thing you might want to try:

look at the execution plan to find clues

Replace the * with the actual column names - don't know why, but it sometimes helps

add index to each column and try (until you find the best one)

try to change the primary key's index type - try Clustered instead of Non-Clustered

try to create a view upon this table and query from the view instead

hope you could resolve it...

Well selecting * from a table is always going to be fairly slow as you are asking the DBMS to scan the entire table for every row and return the results. If you want specific results and to optimize, I'd recommend you modify the queries to select the specific columns you want.
Eg.

select * from TblJobs.JobTitle, TblJobs.JobDescription


As for the space between your text in your job description resulting in a faster retrieval time, this is more of a guess but could it be due to the indexing nature specified by you or your DBMS when you set up the table? The extremely long continuous string might take longer to read or determine where to proceed to retrieve your data. That or the DBMS has caching mechanisms in place that may have made the second search considerably quicker after the first execution (See here)

Hope this helps.

select * from TblJobs.JobTitle, TblJobs.JobDescription

select TblJobs.JobTitle, TblJobs.JobDescription from TblJobs

This sounds like it might be a caching issue. In brief:

So, my theory is:

Select * from TblJobs

Select * from TblJobs

To test this, use the command DBCC DropCleanBuffers. This will clear out the buffer cache, requiring all subsequent queries to read from disk. So:

DBCC DropCleanBuffers

DBCC DropCleanBuffers

Select * from TblJobs

Select * from TblJobs

DBCC DropCleanBuffers

Select * from TblJobs

Select * from TblJobs

Much depends on how much data is being read and how much memory you have on the machine--but hey, memory gets pretty big these days, and I suspect this will not be an issue.

You can mix in runs of Select JobId, JobTitle, JobDescription from TblJobs. That returns the exact same set of data, and it should make no difference to your execution times.

Select JobId, JobTitle, JobDescription from TblJobs

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.