|
I have inherited some code that runs like a pig (I think because of numerous udf calls).
I am not yet sufficiently skilled at T-SQL to figure out how to fix this, maybe someone can give me a couple of pointers?
Structure:
5 Tables:
PType ID Name
PCat ID PTypeID Name
Prod ID PCatID Name
Prop ID PTypeID Name
PropVal ID ProdID PropID Value
all ID's are Primary. All xxxID's are Foreign
In order to access PropVal Value data, the previous dev has used a udf to return the value.
This now means that heaps of sp's are littered with this function, causing what I believe are pretty serious
performance issues.
Example:
SELECT p.Name, Case When pt.Name LIKE 'IN%'
Then dbo.fGetPropVal(p.Name.'SHOT')
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then dbo.fGetPropVal(p.Name,'LEN')
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc
ON p.PCatID = pc.ID,
JOIN PType pt
ON pc.PTypeID = pt.ID
...
(udf - like...)
SELECT Value
FROM PropVal pv
JOIN Prop pr
ON pv.PropID = pr.ID
JOIN Prod p
ON pv.ProdID = p.ID
JOIN PCat pc
ON p.PCatID = pc.ID
JOIN PType pt
ON pc.PTypeID = pt.ID
WHERE p.Name = @PName
AND pr.Name = @PropName
Is there much I can do with this?
Any advice or pointers in the right direction will be most appreciated.
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Disclaimer: The following codes may contain several typos etc. They are just provided to give some ideas 
Perhaps the easiest transformation would using a scalar instead of UDF. The following won't be the best version when considering performance, but it allows the optimizer to reorganize the query if possible:
SELECT p.Name,
Case When pt.Name LIKE 'IN%'
Then (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE p1.Name = p.Name
AND pr1.Name = 'SHOT')
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE p1.Name = p.Name
AND pr1.Name = 'LEN')
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc ON p.PCatID = pc.ID,
JOIN PType pt ON pc.PTypeID = pt.ID
...
The next version could be using joins. Those scalars can be dropped to inline views. However, without knowledge about the amount of rows, logic etc, it's hard to describe the correct transformation. It coud be something like:
SELECT p.Name,
Case When pt.Name LIKE 'IN%'
Then iv1.Value
Else 0
End As 'SHOT',
Case When pt.Name LIKE 'EX%'
Then iv2.Value
Else 0
End As 'LEN',
...
FROM Prod p
JOIN PCat pc ON p.PCatID = pc.ID,
JOIN PType pt ON pc.PTypeID = pt.ID
LEFT OUTER JOIN (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE pr1.Name = 'SHOT') iv1 ON iv1.Name = p.Name
LEFT OUTER JOIN (SELECT Value
FROM PropVal pv1
JOIN Prop pr1 ON pv1.PropID = pr1.ID
JOIN Prod p1 ON pv1.ProdID = p1.ID
JOIN PCat pc1 ON p1.PCatID = pc1.ID
JOIN PType pt1 ON pc1.PTypeID = pt1.ID
WHERE pr1.Name = 'LEN') iv2 ON iv2.Name = p.Name
...
Hope this helps you.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Make sure all the FKs have indexes (no code change)
Create a view with the UDF code without the where clause. You could then target your worst performing procs and join to the view instead of using the udf
Also I think in and like in the case statement may be a performance issue, try changing it to
when left(pt.name,2) = 'EX'
It may give you better results.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: Also I think in and like in the case statement may be a performance issue, try changing it to
when left(pt.name,2) = 'EX'
It may give you better results.
Actually that could lead to weaker performance. When the LIKE is used as in the query was, it's possible for the database to use horizontal index seek.
If the column is wrapped inside a function, the database first has to evaluate the function for each row in the table and only after that it can eliminate rows not needed so this approach leads to full table scan for the table.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yes, if you don't lead the condition with a wildcard (% or ?) then, using LIKE is much faster than =. So:
WHEN pt.name LIKE 'EX%'
will be much faster that:
WHEN left(pt.name, 2) = 'EX'
For one, the LEFT() function is being called on each row in the result set and then comparing the result against a string constant; whereas with the LIKE with the wildcard at the end will bypass the functio call and cause the engine to utilize the indexes (if any) to find matches on the string comparison. I'm not sure why, but from my experience whatever it's doing under the hood causes LIKE to generally out-perform =.
Similar to previous suggestions I'd move the value lookup out of a UDF either into the query itself, a CTE (Common Table Expression), an inline view, or a persistent view. They'll probably all give about the same performance, the choice would just depend on your needs. Since you mention it being used in many places, offhand it seems the persistent view would be the best option.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Lets consider a simple scenario. Students hand in their homework and an application is used to enter the date and time the homework is handed in and stored in the database. We need to keep track of students who hand in their work on time. So if a student always hands in homework, we can assign "Always" or some code to a column within the student record. If the student misses to hand work in on time a predefined number of times then status will become "Sometimes" or some other code.
The status can go from "Always" to "Never" but it can also go from "Never" to "Always".
We can start every student with "Always" and they will either stay at that level or drop lower.
How do you recommend I can fulfill such a requirement? Another table? A trigger?
|
|
|
|
|
Options as I see them
Store status on the student record and update every time the homework is touched (I would use a stored proc and NEVER a trigger)
Don't store status at all if it is only used for output, build the logic into a stored proc that services the request about status.
Create a view of the student table which calcs the status
There is no BEST way to do this, they are dependent on your requirements, style and preferences.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
One way to look at this problem is that you have 2 dates for each assignment.
You could create a function that counts the number of assignments given and determine the number of assignments that are late. Use a ration of late / total to determine a percentage of late assignments and when the ratio is too high, you could call them "sometimes" or "always" late.
Just a thought.
|
|
|
|
|
Then you could add a weighting system so the most recent performance has a stronger bias to give a better profile.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I would keep the data in few entities and implement the logic in an UDF. Something like:
- Homework: name, target date etc
- Assignment: student, return date, possibly new target date if it's allowed to get more time etc
- parameter table: category name (always, sometimes etc), min difference between target date, max difference between target date and so on
Then I would add a computed column on assignment which calculates the difference between return and target date. After that I would implement the UDF which defines the logic how you want to categorize the student (based on his/hers assignments and their calculated differences.
This logic could be parameterized with more parameter tables if you want.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
the problem is that i have a huge database builtin MS Access due to such burden i want to Convert To Oracle is it Possible to Convert Whole DataBase to Oracle
|
|
|
|
|
Yes, you can convert the data from MS-Acess to Oracle quite easily. As a first step, you could convert the data and leave the MS-Access application alone. You would use "Linked Tables" back to the Oracle database. This would store all of the data in Oracle, but your programs would reside in MS-Access.
|
|
|
|
|
i want to program it from oracle then any good article
|
|
|
|
|
How i can import excel to an existing table in SQL server
|
|
|
|
|
By using DTS, in SQLServer 2000. or Analysis Services in 2005.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Hi,
Did u get the answer, if you are satisfied please give your rank.
Thanks,
Aleem Mohammad
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
Hopefully you're using SQL Server 2005.
Make sure you have SSIS enabled (running):
Start|All Programs|Microsoft SQL Server 2005|Configuration Tools|SQL Server Surface Area Configuration|Surface Area Configuration for Services and Connections|Intergration Services
Click ok. Close the Surface Area window.
Next launch SSMS and in the object explorer select the DB you wish to import into.
Right click|Tasks|Import
This should start the import wizard. From here you should be able figure things out.
HTH
The world is a stage and most of us are desperately unrehearsed.
—Sean O’Casey, Playwright
|
|
|
|
|
SSIS would be an easy way as Jerry Hammond guided. If you're not able to use it for some reason, you can save the excel sheet as CSV file and then use bcp utility[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Yes, what jerry has suggested is right. The DTS is called as "Analysis Services" in SQL Server 2005.
If you are using SQL Server 2000 then select the Database and in tools u will find DTS packages then use import or export whichever u want. Do little much research to schedule it as job, as mentioned by myk that is called "BCP".
If you want you can have a small UI also to execute your DTS package from VB or C# code.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
|
Hi,
I have stored proc in which an SQL query is being executed which may timeout some times. I got that error using the code it is showing one number but when I searched in google for the same, but google is showing another number for query time out.
Now my question is how can I get that the query is timed out. Will that error number be in some limit from this number to this number is for query time out or it changes from OS to OS or does it depends in SQL server version.
Please give me hints about it sot that I can show the appropriate message to the user in exception. I would be very much thankfull to all of you.
Thanks,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
modified on Friday, November 21, 2008 9:10 AM
|
|
|
|
|
What was the error number you got and from which SQL Server version?
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
The error number that I am getting from the program is : 2147184992 and in google I am getting another number.
Which should I take. And how should I resolve this issue.
Thanks,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
|
That number sounds more like the HRESULT.
When the exception occurs, have a look at Number and Message properties of the SqlException.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
Hi,
Thanks for the answer, whats my dialomma is if the number changes for another machine when we deploy it, then our code can not show the proper message thats why I am asking for the accurate way to catch the query timed our exceptions.
Can u please help me in this.
Thanks a Lot,
Aleem Mohammad.
Thanks & Regards,
Md. Abdul Aleem
NIIT technologies
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
