Hi, when running the following stored procedure:
ALTER PROCEDURE [dbo].[GetWerknemersBijLeidinggevende]
@.LeidinggevendeIDint,
@.Startint = 1,
@.Limitint = 25,
@.Sofinummerint =NULL,
@.Achternaamnvarchar(128) =NULL,
@.Functienvarchar(64) =NULLAS
WITH OrderedAS(SELECTROW_NUMBER()OVER (ORDER BY Achternaam)AS RowNumber,
Persoon.*
FROM Persoon
INNERJOIN Dienstverband
ON Persoon.ID = Dienstverband.PersoonID
INNERJOIN Bedrijfsonderdeel
ON Bedrijfsonderdeel.ID = Dienstverband.BedrijfsonderdeelID
INNERJOIN Leidinggevende
ON Bedrijfsonderdeel.ID = Leidinggevende.BedrijfsonderdeelID
WHERELeidinggevende.Begindatum <=getdate()
AND
(
Leidinggevende.Einddatum >getdate()
OR Leidinggevende.EinddatumISNULL)AND Leidinggevende.PersoonID = @.LeidinggevendeID
AND(Sofinummer = @.SofinummerOR @.SofinummerISNULL)AND(AchternaamLIKE @.Achternaam
OR AchternaamPartnerLIKE @.Achternaam
OR @.AchternaamISNULL))SELECT *
FROM Ordered
WHERE RowNumberbetween @.Startand (@.Start + @.Limit - 1)
When I run this in the database and fille de LeidinggevendeID parameter with a value I get a few rows returned, however when I run the following code:
[DataObject(true)]
public class PersoonFactory
{
[DataObjectMethod(DataObjectMethodType.Select,false)]
public static IList WerknemersBijLeidinggevende(int ldgID,int start,int max)
{
IList list =new List();
SqlDataReader rdr =null;
SqlConnection connection = DatabaseProvider.Connection;
SqlCommand command =new SqlCommand("GetWerknemersBijLeidinggevende", connection);
command.Parameters.AddWithValue("LeidinggevendeID", ldgID);
command.CommandType = CommandType.StoredProcedure;
try { connection.Open(); rdr = command.ExecuteReader(CommandBehavior.CloseConnection);while (rdr.Read())
{
Persoon pers =new Persoon();
pers.ID = rdr["ID"]as int?;
pers.Achternaam = rdr["Achternaam"]as string;
pers.AchternaamPartner = rdr["AchternaamPartner"]as string;
pers.Achtertitels = rdr["Achtertitels"]as string;
pers.DatumOverlijden = rdr["DatumOverlijden"]as DateTime?;
pers.Geboortedatum = rdr["Geboortedatum"]as DateTime?;
pers.Geslacht = rdr["Geslacht"]as string;
pers.Middentitels = rdr["Middentitels"]as string;
pers.Naamgebruik = (int)rdr["Naamgebruik"];
pers.Sofinummer = rdr["Sofinummer"]as string;
pers.Voorletters = rdr["Voorletters"]as string;
pers.Voortitels = rdr["Voortitels"]as string;
pers.Voorvoegsel = rdr["Voorvoegsel"]as string;
pers.VoorvoegselPartner = rdr["VoorvoegselPartner"]as string;
list.Add(pers);
}
}
catch {throw;
}
finally {if (rdr !=null) rdr.Close();
else connection.Close();
}
return list;
}
I get 0 rows all of a sudden. Any idea why?
Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server.
BTW, I notice in the SP you compare a column with a parameter in this way:
Sofinummer = @.Sofinummer
OR @.SofinummerISNULL
Since the @.Sofinummer can be null, so when both Sofinummer and @.Sofinummer are null, the expression "Sofinummer = @.Sofinummer" will return true if ANSI_NULLS option is off; and will return false when ANSI_NULLS if on. This won't cause confusion in this procedure since you add a "OR @.SofinummerISNULL" to deal with the null situation. However, we can use a ISNULL function to avoid such potential confusing result:
Sofinummer = ISNULL(@.Sofinummer,Sofinummer)
I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.
For example
DECLARE @.x varchar(20)
SET @.x=NULL
SELECT *
FROM MyTable
WHERE (x=@.x OR @.x IS NULL)
without even executing the query, the where clause could be optimized to WHERE ((x=@.x) OR NULL IS NULL) -> WHERE ((x=@.x) OR TRUE) -> WHERE TRUE.
In the case @.x=1 it could be optimized to: WHERE ((x=@.x) OR 1 IS NULL) -> WHERE ((x=@.x) OR FALSE) -> WHEREx=@.x
|||"Your code works fine in my testing. You may need to capture a SQL Profiler trace to see what has been passed to SQL Server in the call of the SP, and what has been returned from SQL Server."
Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?
Regards,
Dirk Louwers
|||
Motley:
I always thought that the original posters way was the better approach since one half of x=@.x OR @.x IS NULL can be quickly optimized out of the query (and not need to be evaluated on each row) since one half of it can be determined before the query is even executed and simplified, or isn't SQL Server that intelligent yet? That is unless SQL Server has been specifically told that x=ISNULL(@.x,x) is a special case, and also knows how to optimize that out of the query when @.x is/is not NULL.
Yes, I agree with you on this
What I mean is that usingx=@.x where both x and @.x can be null may lead to nondeterministic result, based on the ANSI_NULLS setting.
Actually using x=ISNULL(@.x,x) will always add a nested loop in the execution plan so the performance may drop down when the table is huge. However when using "x=@.x OR @.x IS NULL", if @.x is null, SQL Server is not aware that actually the parameters are NULL and such clause won't filter any record, thus in the execution plan it may still uses index/table scan [+nested loop (if the query joins mutilple tables)] to retrieve the record.
To get max performance for such query, the best way it to execute different statements according to whether the parameters are null.If queries with such searching criterials are in a Stored Procedure, sometimes we can improve the performance by simply passing the values of the parameters to local variables defined in the SP, and then use the local variables in the queries. We have discussed such issue in a post In SQL2005, we have a new option new option for optimizing queries--Plan Guides. I have updated the post, please check it:
http://forums.asp.net/2/1377161/ShowThread.aspx
|||
Thanks for the reply. Well, since I am rather new to MS SQL (come from a MySQL background): How do I capture a SQL Profiler trace? Could you refer me to any documentation regarding this procedure?
Go to All Programms->Microsoft SQL Server2005->Performance Tools->SQL Server Profiler. You have to add Events and DataColumns according to what you want to trace. Since we just want to see what's happened when the stored procedure is executed, just start a trace using the TSQL_SPs template. To learn more about SQL Profiler, you can start from here:
http://msdn2.microsoft.com/en-us/library/ms187929.aspx
|||Unfortunately I am using SQL Server 2005 Express on my development machine and the profiler doesn't seem to be included. Is there another options available other than moving the whole development project over to the production server and run it there?
|||If you have SQL2005 on your production server, you can use the Profiler on the production server to capture trace on your development server. If you don't have Profiler, you can print the SQL command just before you executing it to see whether it looks like you expected.
No comments:
Post a Comment