| Author |
Topic |
|
Dasman
Yak Posting Veteran
79 Posts |
 Posted - 2011-06-20 : 17:56:48
|
Dear All,Here are the Tables and Columns I have:E (Events)ID | RID | EventTypeand R (Recordings)ID | PIDNow R.ID = RID.Now in the function I run - The tables are inner joined (R.ID = RID), and it runs 3 conditions in the where clause. The E table has over half a million rows but for one RID and the related PID - it has only about a 1000 rows of data.To speed up my function, I want the first thing for it to do is to separate out a table for only the CERTAIN PID AND RID and its correlating rows and then proceed to have its where condition clauses and inner joins. Currently the function searches through the half a million rows instead of the single record that corresponds to a PID and RID.If you would like some clarification dont hesitate to post, but can someone please help. Hope I was detailed. Dasman ==========================Pain is Weakness Leaving the Body. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-20 : 18:29:21
|
| This is the Actual function. I am basically asking this function takes a while to do. Is there a more efficient way to execute this?ALTER FUNCTION [dbo].[NAME] (@PID int, @RID int)RETURNS TABLE ASRETURN (SELECT COUNT(dbo.Events.ID) AS NAMEFROM dbo.Events INNER JOIN (SELECT StartTime FROM dbo.EventsWHERE (EventType LIKE '%condition1%') AND StartTime > (SELECT * FROM dbo.StartTime (@PID, @RID)) AND StartTime < (SELECT * FROM dbo.StopTime (@PID, @RID))) Event_times ON dbo.Events.StartTime < Event_times.StartTime AND dbo.Events.StopTime > Event_times.StartTimeINNER JOIN dbo.Recordings ON dbo.Events.RID = dbo.Recordings.ID INNER JOIN dbo.Patients ON dbo.Recordings.PID = dbo.Patients.IDWHERE (dbo.Patients.ID = @PID) AND (dbo.Recordings.ID = @RID) AND (dbo.Events.EventType LIKE 'Condition2'))==========================Pain is Weakness Leaving the Body. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-21 : 15:17:41
|
| Well it exists in a stored procedure and when I execute the stored procedure - it prompts the insertion of the PID and RID. After that it takes about 5-8 seconds and then gives me results.I am just curious as to maybe I can somehow shorten it?==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-22 : 12:44:43
|
| Hey Tara,I actually figured out the problem - I had to bound one the inner select statements by the RID. Since I did not it originally considered over 500,000 records. However, with the constraint, it only considers a 1000 so the whole stored procedure executes within 3 seconds versus 8-10 as it did before.Thanks for your help and sorry I didn't provide all the details in the beginning.Best,Dasman==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-22 : 13:35:34
|
| Make it a query instead of a function? Well the benefit is I can enter hundreds of PIDs and RIDs and it can get the results out for 10 functions through one stored procedure - versus making 10 queries and changing the values every time for each new set of PID,RID.Thanks!==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-22 : 13:42:04
|
| I don't see why you would need to make 10 queries if you removed the function. All that would need to be changed is you'd put the code from the function in the stored procedure, and then the query optimizer would be able to use a better plan assuming you have indexes to support it. View the execution plan of your current code to see why the function is bad. You'll notice a table scan or a clustered index scan on the function call, not good.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-22 : 15:50:29
|
| Tara,In the Stored Procedure there are 10 separate functions. One of those functions is the one that I pasted in the above thread. All of the 10 can accept the parameters PID and RID and out put different results. So I can do what ur saying but just putting the query code in the stored procedure - but id just have to make a separate stored procedure correct?Dasman==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-22 : 16:08:31
|
| can u run several queries within a same stored procedure?==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-06-22 : 16:25:27
|
| How is that syntax in that work?like:SELECT*FROM TABLEend SELECT*FROMend?==========================Pain is Weakness Leaving the Body. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Dasman
Yak Posting Veteran
79 Posts |
Posted - 2011-07-20 : 13:07:59
|
| Hello Again,I am actually using SQL Reporting Services Report Builder 3.0 2008 R2.I have several queries in a stored procedure and I have imported that Dataset - the service immediately recognized each output column.Is having several queries in a stored procedure the fastest way to run this program?Best,Dasman==========================Pain is Weakness Leaving the Body. |
|
|
|
|
Pulling Only a certain amount of rows from a Table