How to select 200k data faster in sql

Question

How to select 200k data faster in sql

jaquino012

SSC-Addicted

Points: 431
More actions
December 10, 2015 at 12:09 am

#310102

Hi experts,
I hope someone help me on how can i make my SQL script faster.
Here is a background on my SQL:
i have a create a offline dictionary for my front end application.
actually it has less than 300k data that i have select/load from my SQL server.
It takes a lot of time in loading the data. below is my script.
declare @Dict varchar(max)=''
select @Dict=@Dict + '"'+Word+'",' +' ' from Dictionary order by Word asc
select @Dict
Thanks for your help.

Viewing 15 posts - 1 through 15 (of 18 total)

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

Grant Fritchey SSC Guru Points: 398881 More actions · Answer 1

A single SELECT statement without any kind of filtering offers very little in the way of query tuning. A scan is the best way to access the data in this case. So, the only option you have is tuning your hardware, ensuring that you have as fast I/O as possible by adding disks and disk controllers to the server to speed things up.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

ben.brugman SSChampion Points: 13369 More actions · Answer 2

select '"'+Word+'",' +' ' from Dictionary order by Word asc FOR XML PATH('')

My expectation is that this will work a lot faster.

Please do share your timings.

Ben

ben.brugman SSChampion Points: 13369 More actions · Answer 3

Grant Fritchey (12/10/2015)
A single SELECT statement without any kind of filtering offers very little in the way of query tuning. A scan is the best way to access the data in this case. So, the only option you have is tuning your hardware, ensuring that you have as fast I/O as possible by adding disks and disk controllers to the server to speed things up.

Problem is not the selection but the building of the string.

When the string is short, the building does not take too much time.

But when the string gets longer each word that is added takes a lot of time.

Timings:

Number of words For XML Concatenation

10 00.000 00.000

100 00.000 00.000

1000 00.06333 00.0533

10000 00.03 00.41

20000 00.046 01.536

30000 00.133 32.38

40000 00.06 163.74

Timing is not completely consistent, but as can be seen that concatenation of the string becomes far slower when the string is growing.

Ben

The code used for testing:

-- Generate a Dictionary with a large number of words.

;

WITH

L0 AS(SELECT 0 AS c UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0), -- 4

L1 AS(select 0 as x from L0 A, L0 B, L0 C, L0 D), -- 4 ^4 = 256

L2 AS(select 0 as x from L1 A, L1 B, L1 C, L1 D), -- (4 ^ 4) ^4 = 4 Giga

L9 AS(Select row_number() OVER(PARTITION BY 1 order by x ) as P from L2)

select top 40000 'word_'+convert(varchar(8),P) Word into Dictionary from L9

-- Selection with a FOR xml construction

declare @starttime datetime = getdate()

select '"'+Word+'",' +' ' from Dictionary order by Word asc FOR XML PATH('')

select convert(float,GETDATE()-@starttime)*24*60*60

-- Selection with a concatenation construction

Set @starttime = getdate()

declare @Dict varchar(max)=''

select @Dict=@Dict + '"'+Word+'",' +' ' from Dictionary order by Word asc

select @Dict

select convert(float,GETDATE()-@starttime)*24*60*60

drop table Dictionary

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 4

If you pulled the data directly into a data reader and then processed that down on the client, is that faster?

jaquino012 SSC-Addicted Points: 431 More actions · Answer 5

Hi,

Thanks for your reply.

@Grant Fritchey

I have thinking the same way.i takes so much resources to query from my back end to front end.

@ben.brugman

Thanks for the input and it takes only a second to load the data. And you are right and the data is to long to contain in a single data type in my front end.

so i decided to put all my word in a text file ito lessen the load of my resources and that will be my offline dictionary.

@steve-2 Jones - SSC Editor

yes. i have been using that approach of my client side.

Thanks again for your input.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 6

Do you have a unique index on Word? You don't want SQL to have to sort the words every time -- that will be extremely slow. Also, if you allow ad-hoc inserts to the table, be sure to set the fillfactor below 100, and rebuild that index whenever it gets even 10+% fragmented, since you're looking for very good speed.

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".

jaquino012 SSC-Addicted Points: 431 More actions · Answer 7

HI,

@scottpletcher

Thanks for the input. i will find another approach on this. since i have a big dictionary to search for each user.

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 8

ScottPletcher (12/10/2015)
Do you have a unique index on Word? You don't want SQL to have to sort the words every time -- that will be extremely slow. Also, if you allow ad-hoc inserts to the table, be sure to set the fillfactor below 100, and rebuild that index whenever it gets even 10+% fragmented, since you're looking for very good speed.

If new words being added is a somewhat infrequent event, using a Fill Factor of other than 100 would be a source of slowness and unneeded resource usage. For example, if you use a Fill Factor of 90, then you must read 10 pages to read 9 pages of content. Of course, that only matters for scans and not singleton seeks... but then the Fill Factor and levels of fragmentation don't actually matter for singleton seeks anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 9

jaquino012 (12/10/2015)
Hi experts,
I hope someone help me on how can i make my SQL script faster.
Here is a background on my SQL:
i have a create a offline dictionary for my front end application.
actually it has less than 300k data that i have select/load from my SQL server.
It takes a lot of time in loading the data. below is my script.
declare @Dict varchar(max)=''
select @Dict=@Dict + '"'+Word+'",' +' ' from Dictionary order by Word asc
select @Dict
Thanks for your help.

Ok... hold the phone just a minute. What do you mean by an "offline dictionary"? Do you mean that you're trying to build a dictionary [font="Arial Black"]FILE[/font]?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jaquino012 SSC-Addicted Points: 431 More actions · Answer 10

Hi,

@jeff Moden

Yes, i have been working on a simple dictionary with a limited words and it is working fine.

but what if the words are so big the i cant query it on a SQL, that is why i have asking if there is a better way to load a 300k words from SQL to my from end.

And my last solution is put it on a text file if that is a possible.

ScottPletcher SSC Guru Points: 101091 More actions · Answer 11

Jeff Moden (12/12/2015)
ScottPletcher (12/10/2015)
Do you have a unique index on Word? You don't want SQL to have to sort the words every time -- that will be extremely slow. Also, if you allow ad-hoc inserts to the table, be sure to set the fillfactor below 100, and rebuild that index whenever it gets even 10+% fragmented, since you're looking for very good speed.
If new words being added is a somewhat infrequent event, using a Fill Factor of other than 100 would be a source of slowness and unneeded resource usage. For example, if you use a Fill Factor of 90, then you must read 10 pages to read 9 pages of content. Of course, that only matters for scans and not singleton seeks... but then the Fill Factor and levels of fragmentation don't actually matter for singleton seeks anyway.

Using a fillfactor of 100 is extraordinarily risky for such a short row if, for example, there might ever be a trigger on this table or snapshot isolation on the db. Combined with the chance of a few random insertions, 98 or 99 seems more reasonable to me. I noted above that 10% freespace is too high for large tables, but 0% has very real risks too, except on 99.9% read-only tables.

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".

Steve Jones - SSC Editor SSC Guru Points: 736160 More actions · Answer 12

The best way to load a dictionary of 300k words is store it as an Access file and download it. You can index the local file and query it quickly.

Is there some idea of what you're doing here? Are you looking for singletons, specific words or doing a general query against a lot of text as a spell checker?

jaquino012 SSC-Addicted Points: 431 More actions · Answer 13

Hi,

@steve-2 Jones - SSC Editor

Yes iam doing a general query for a spell checker.

Does querying it using a Access is much more faster than SQL?

Sergiy SSC Guru Points: 110208 More actions · Answer 14

jaquino012 (12/14/2015)
Hi,
@steve-2 Jones - SSC Editor
Yes iam doing a general query for a spell checker.
Does querying it using a Access is much more faster than SQL?

Actually, using SQL is much faster.

But you're not using SQL.

You're trying to unload your dictionary from SQL to some sort of file which would be accessible in disconnected mode.

In this case using Access is faster than using a flat file.

I don't quite understand why do you need to unload the whole dictionary every time you query a word.

If you want to have all the words available off-line - copy all of them once into some sort of file and update it when a new word is invented and added to your main dictionary.

_____________
Code for TallyGenerator