How to reduce the execution time of a query?

Question

How to reduce the execution time of a query?

Viewing 8 posts - 31 through 37 (of 37 total)

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

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

Charmer (3/28/2012)
Sean Lange (3/28/2012)
Do you have to interface with a mainframe or some other older system? The reason I ask is all of your names are so short. It is nearly impossible to be able to suggest much in the way of a redesign because all the table and column names are so cryptic.
All of these tables that have no keys are going to perform worse and worse as the amount of data expands. You said something about these tables having millions of records. Without keys you are fighting a losing battle. You could literally have hundreds of rows where every single column is null because every single column allows nulls.
interface with a mainframe? Sean, I don't understand this one....because they gave me the back up of the database and i am working on it...that's all i know...if you brief me, i would ask my manager so that i can give you good answer...
and yes there is no key columns even in a single table that i deal with..but i have to extract the data...i don't know what i am going to do....each query takes more than 3 hours to execute...I'm going crazy......

You asked for some advice on structuring your data better and I was trying to figure out why every table and column have names that are only 6 characters.

Sounds like it is time to hire a consultant. No offense but you really need to hire a professional for this.

_______________________________________________________________

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/

S_Kumar_S SSCrazy Eights Points: 9037 More actions · Answer 2

You mentioned that you added [NameIDRef] [varchar](15) NULL to the table [PSAJCK](your biggest table, right??) and it is unique. Does it contain null values? if not it can be your primary key.

Very basic idea this is, but we do skip basics sometimes.....

Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.

Charmer SSChampion Points: 14903 More actions · Answer 3

SQLKnowItAll (3/28/2012)
You may not want to hear this... but I suggest hiring a consultant who can sit down and understand the business requirements and design this properly. We won't be able to do this from the limited amount of information given in the threads. We have tables here with 10's of millions of rows and queries run in seconds joining them. That's because they are designed properly (well... mostly 😉 ). There is only so much that you can do with a query to enhance its performance, after that you have to address the real issue of design.

yes i agree with you friend...

but i have to do this only with the help of query...and adding some indexes with the table...

any way i have to thank you for your help so far..

Thanks,
Charmer

Jared SSC Guru Points: 61793 More actions · Answer 4

Charmer (3/28/2012)
SQLKnowItAll (3/28/2012)
You may not want to hear this... but I suggest hiring a consultant who can sit down and understand the business requirements and design this properly. We won't be able to do this from the limited amount of information given in the threads. We have tables here with 10's of millions of rows and queries run in seconds joining them. That's because they are designed properly (well... mostly 😉 ). There is only so much that you can do with a query to enhance its performance, after that you have to address the real issue of design.
yes i agree with you friend...
but i have to do this only with the help of query...and adding some indexes with the table...
any way i have to thank you for your help so far..

If someone asks you to make a car fly by only changing the fuel or the engine, could you do it? You may get it to go fast enough to reach liftoff speed, but without wings it will not fly... Your database needs wings, not a redesign of the engine or fuel.

Jared
CE - Microsoft

Charmer SSChampion Points: 14903 More actions · Answer 5

Sean Lange (3/28/2012)
You asked for some advice on structuring your data better and I was trying to figure out why every table and column have names that are only 6 characters.
Sounds like it is time to hire a consultant. No offense but you really need to hire a professional for this.

That is ok Sean...I will try to figure it out by my own if any luck i have....else i would come up with the problems over here so that you people can help me...Thank you for your suggestion Sean...I appreciate it..

Thanks,
Charmer

Charmer SSChampion Points: 14903 More actions · Answer 6

SQLKnowItAll (3/28/2012)
If someone asks you to make a car fly by only changing the fuel or the engine, could you do it? You may get it to go fast enough to reach liftoff speed, but without wings it will not fly... Your database needs wings, not a redesign of the engine or fuel.

I get it...i hope you understand my situation...let me talk to my authorities about this problem...that's the only luck i have..

Thanks,
Charmer

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

Charmer (3/28/2012)
Sean Lange (3/28/2012)
You asked for some advice on structuring your data better and I was trying to figure out why every table and column have names that are only 6 characters.
Sounds like it is time to hire a consultant. No offense but you really need to hire a professional for this.
That is ok Sean...I will try to figure it out by my own if any luck i have....else i would come up with the problems over here so that you people can help me...Thank you for your suggestion Sean...I appreciate it..

At the very least you need define primary keys for every table. Without some way to identify a row you fighting a war you can't win.

Then you should change your nvarchar(1) to nchar(1). Every column you change here will reduce the storage space by at least bytes per column. http://msdn.microsoft.com/en-us/library/ms186939%28v=sql.105%29.aspx

You need to evaluate if all your columns can really be null.

If at all possible rename tables and columns with a bit more meaningful names. This won't directly affect performance but it will provide an atmosphere that is less hostile.

Get some books on data modeling and understand how to make this better. If the only resource is you, you are going to have to get a better understanding of what you are dealing with. We can provide some assistance but you have a really big challenge on your hands here.

_______________________________________________________________

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/

Charmer SSChampion Points: 14903 More actions · Answer 8

Sean Lange (3/28/2012)
At the very least you need define primary keys for every table. Without some way to identify a row you fighting a war you can't win.
Then you should change your nvarchar(1) to nchar(1). Every column you change here will reduce the storage space by at least bytes per column. http://msdn.microsoft.com/en-us/library/ms186939%28v=sql.105%29.aspx
You need to evaluate if all your columns can really be null.
If at all possible rename tables and columns with a bit more meaningful names. This won't directly affect performance but it will provide an atmosphere that is less hostile.
Get some books on data modeling and understand how to make this better. If the only resource is you, you are going to have to get a better understanding of what you are dealing with. We can provide some assistance but you have a really big challenge on your hands here.

yes Sean,you are correct that i am the only one resource to take care of this one ...and i know i have big challenge...i am keep fighting on it not to get a perfect solution but some better solution for this...that's all i can do ....if i need , you people guide me ..that's all i need from you guys...

Thanks,
Charmer