Improve performace of a simple query

Question

Post reply

Improve performace of a simple query

Viewing 12 posts - 16 through 26 (of 26 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

We still need definitions for

t_UserPreferences

t_regions

And we still need sample data and desired output.

There are lots of things that can be done to improve the performance of this. One thing you can do is to toss that split function. Take a look at the link in my signature about parsing strings. You will find a much faster splitter than the looping you have.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Lynn Pettis (6/5/2012)
From OP's second post:

OK so we did have indexes (although at first glance they don't seem to help much here). The rest of everything I said is still true. We still have poor datatype choices, nested RBAR scalar functions and data that isn't normalized.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Tripz Ten Centuries Points: 1205 More actions · Answer 3

Table bbbb is nothing but t_UserPreferences

and table cccc is nothing but resumes

my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Tripz (6/5/2012)
Table bbbb is nothing but t_UserPreferences
and table cccc is nothing but resumes

So bbbb is the same as t_UserPreferences?

What about t_regions?

my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function

I understand the point of the function, to get around a terrible way of storing data. The methodology in your split function is terribly slow. Read the article in my signature, it demonstrates how slow that is and a different approach that is lightyears faster. I would not be surprised if just that takes half of the time off your query. The real problem is that for the data you are retrieving it should take 1-2 seconds.

Unless you provide all the details nobody can really do much to help. This is the third time I have asked for ddl (we are still missing one table), still don't have any sample data, still don't have desired output.

We can't write queries against tables that don't exists and no data with no idea of what the output should be. If you can provide those details we can help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 5

Sean Lange (6/5/2012)
Tripz (6/5/2012)
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
I understand the point of the function, to get around a terrible way of storing data.

I wonder since when 3NF is considered to be terrible way of storing data?

Alex Suprun

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Alexander Suprun (6/5/2012)
Sean Lange (6/5/2012)
Tripz (6/5/2012)
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
I understand the point of the function, to get around a terrible way of storing data.
I wonder since when 3NF is considered to be terrible way of storing data?

my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function

uummmm they have comma delimited data in a single column. That is by no means 3NF.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Alexander Suprun SSCertifiable Points: 6253 More actions · Answer 7

Sean Lange (6/5/2012)
Alexander Suprun (6/5/2012)
Sean Lange (6/5/2012)
Tripz (6/5/2012)
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
I understand the point of the function, to get around a terrible way of storing data.
I wonder since when 3NF is considered to be terrible way of storing data?
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
uummmm they have comma delimited data in a single column. That is by no means 3NF.

So why don't normalise it to 3NF, and to what terrible way of storing data are you refering then?

Alex Suprun

Sean Lange SSC Guru Points: 286590 More actions · Answer 8

Alexander Suprun (6/5/2012)
Sean Lange (6/5/2012)
Alexander Suprun (6/5/2012)
Sean Lange (6/5/2012)
Tripz (6/5/2012)
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
I understand the point of the function, to get around a terrible way of storing data.
I wonder since when 3NF is considered to be terrible way of storing data?
my UserPreferences i.e bbbb has comma separated region ids and category ids, that is the reason to use fn_split function
uummmm they have comma delimited data in a single column. That is by no means 3NF.
So why don't normalise it to 3NF, and to what terrible way of storing data are you refering then?

The same you were talking about on page 2.

Do you keep category and region as comma separated strings? Bad. Bad design. Create 2 separate tables for categories and regions attached to the users.

There is no indication that normalizing this was even on the table. The original query posted is trying to deal with when it retrieves the data.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Tripz Ten Centuries Points: 1205 More actions · Answer 9

Hi,

I removed all the 3 functions and updated the query accordingly and executed it. it is taking 10 sec to execute now.

Thank you for all the help

I am trying to normalize the tables and clean the data types now

geoffrey crombez Mr or Mrs. 500 Points: 552 More actions · Answer 10

using udf's to get column data is a bad idea for performance.

It works well for some rows, but it works loop based, so not using the database engine strength.

So for every extra row, it takes more time.

You should rewrite as JOIN statements so the database engine can execute this SET based.

Kind Regards

Geoffrey

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

Tripz (6/5/2012)
Hi,
I removed all the 3 functions and updated the query accordingly and executed it. it is taking 10 sec to execute now.
Thank you for all the help
I am trying to normalize the tables and clean the data types now

Sweet that is a far cry from the 3 minutes you were getting before!!! I bet with some more tuning you can that 10 seconds down to around 1-2.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Tripz Ten Centuries Points: 1205 More actions · Answer 12

I agree with the points mentioned,

I am trying to tune the db now and I really appreciate each and every ones help to get this done.

I will try to avoid functions in the query and try to tune the db tables as much as I can

Thank you one and all