|
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
|
|
|
|
|
Yes I understood that, but the question is from what property of a SQLException did you take the number? If you took it from ErrorCode, it is not the number for the error, but only a handle to the result which will change every time.
Is the number you posted taken from Number property of a SQLException? Refer to: SqlException Members[^]
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
I would have thought this is a client issue, the error and the text of the error is available in VS and can be reported through normal error trapping. I know this as we get timeout errors every day, they are reported and someone gets slapped for running parallel processes.
Never underestimate the power of human stupidity
RAH
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
