Speed, subselect vs group by
in [Server Configuration]
|
From: Cindy Lee on 25 Jun 2010 17:21 I want to get a list of employees who were all paid on the last payDate in employeeLog before my query date(20100101). I've thought of 2 ways to do it, but I'm not sure which would be faster. I would think the one without the subselect would be faster, but the other way would give me a lot of group by's. select companyId, departmentId,employeeId, employeeFirstName, employeeLastName max(payDate) from employeeLog where payDate< 20100101 and companyId=10 and departmentId=50 group by companyId, departmentId,employeeId, employeeFirstName, employeeLastName vs select companyId, departmentId,employeeId, employeeFirstName, employeeLastName max(payDate) from employeeLog where payDate=(select top(1) payDate from employeeLog where payDate< 20100101 order by payDate desc) I'm guessing the one with the group bys would be faster, or maybe there's another way to do it?
From: Erland Sommarskog on 25 Jun 2010 18:09 Cindy Lee (danparker276(a)yahoo.com) writes: > I want to get a list of employees who were all paid on the last > payDate in employeeLog before my query date(20100101). I've thought > of 2 ways to do it, but I'm not sure which would be faster. I would > think the one without the subselect would be faster, but the other way > would give me a lot of group by's. > > select companyId, departmentId,employeeId, employeeFirstName, > employeeLastName max(payDate) from employeeLog where payDate< 20100101 > and companyId=10 and departmentId=50 group by companyId, > departmentId,employeeId, employeeFirstName, employeeLastName You would need this to match your description: HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > select companyId, departmentId,employeeId, employeeFirstName, > employeeLastName max(payDate) from employeeLog where payDate=(select > top(1) payDate from employeeLog where payDate< 20100101 order by > payDate desc) > > I'm guessing the one with the group bys would be faster, or maybe > there's another way to do it? The only way to find out is to test. And then you only know what was best for that set of data. But if I understand the description of what you are asking for correctly, the second query seems more straightforward. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
From: Cindy Lee on 25 Jun 2010 18:19 On Jun 25, 3:09 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > Cindy Lee (danparker...(a)yahoo.com) writes: > > I want to get a list of employees who were all paid on the last > > payDate in employeeLog before my query date(20100101). I've thought > > of 2 ways to do it, but I'm not sure which would be faster. I would > > think the one without the subselect would be faster, but the other way > > would give me a lot of group by's. > > > select companyId, departmentId,employeeId, employeeFirstName, > > employeeLastName max(payDate) from employeeLog where payDate< 20100101 > > and companyId=10 and departmentId=50 group by companyId, > > departmentId,employeeId, employeeFirstName, employeeLastName > > You would need this to match your description: > > HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > > > select companyId, departmentId,employeeId, employeeFirstName, > > employeeLastName max(payDate) from employeeLog where payDate=(select > > top(1) payDate from employeeLog where payDate< 20100101 order by > > payDate desc) > > > I'm guessing the one with the group bys would be faster, or maybe > > there's another way to do it? > > The only way to find out is to test. And then you only know what > was best for that set of data. > > But if I understand the description of what you are asking for correctly, > the second query seems more straightforward. > > -- > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > Links for SQL Server Books Online: > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Actually the query does work without the HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') because of the group bys. I'll test it out to see which is faster, but I was just wondering if there was a general rule when it comes to this.
From: Cindy Lee on 25 Jun 2010 18:58 On Jun 25, 3:19 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > On Jun 25, 3:09 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > > > > Cindy Lee (danparker...(a)yahoo.com) writes: > > > I want to get a list of employees who were all paid on the last > > > payDate in employeeLog before my query date(20100101). I've thought > > > of 2 ways to do it, but I'm not sure which would be faster. I would > > > think the one without the subselect would be faster, but the other way > > > would give me a lot of group by's. > > > > select companyId, departmentId,employeeId, employeeFirstName, > > > employeeLastName max(payDate) from employeeLog where payDate< 20100101 > > > and companyId=10 and departmentId=50 group by companyId, > > > departmentId,employeeId, employeeFirstName, employeeLastName > > > You would need this to match your description: > > > HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > > > > select companyId, departmentId,employeeId, employeeFirstName, > > > employeeLastName max(payDate) from employeeLog where payDate=(select > > > top(1) payDate from employeeLog where payDate< 20100101 order by > > > payDate desc) > > > > I'm guessing the one with the group bys would be faster, or maybe > > > there's another way to do it? > > > The only way to find out is to test. And then you only know what > > was best for that set of data. > > > But if I understand the description of what you are asking for correctly, > > the second query seems more straightforward. > > > -- > > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > > Links for SQL Server Books Online: > > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Actually the query does work without the > HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > > because of the group bys. I'll test it out to see which is faster, > but I was just wondering if there was a general rule when it comes to > this.- Hide quoted text - > > - Show quoted text - Testing it I found the bottom one almost twice as fast, and I'm pretty sure my query is correct. If I change max to min, I get different results. Also, notice I don't have payDate in the groupBy, otherwise it will return more than 1 date. select companyId, departmentId,employeeId, employeeFirstName, employeeLastName max(payDate) from employeeLog where payDate< 20100101 and companyId=10 and departmentId=50 group by companyId, departmentId,employeeId, employeeFirstName, employeeLastName
From: Cindy Lee on 25 Jun 2010 19:44 On Jun 25, 3:58 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > On Jun 25, 3:19 pm, Cindy Lee <danparker...(a)yahoo.com> wrote: > > > > > > > On Jun 25, 3:09 pm, Erland Sommarskog <esq...(a)sommarskog.se> wrote: > > > > Cindy Lee (danparker...(a)yahoo.com) writes: > > > > I want to get a list of employees who were all paid on the last > > > > payDate in employeeLog before my query date(20100101). I've thought > > > > of 2 ways to do it, but I'm not sure which would be faster. I would > > > > think the one without the subselect would be faster, but the other way > > > > would give me a lot of group by's. > > > > > select companyId, departmentId,employeeId, employeeFirstName, > > > > employeeLastName max(payDate) from employeeLog where payDate< 20100101 > > > > and companyId=10 and departmentId=50 group by companyId, > > > > departmentId,employeeId, employeeFirstName, employeeLastName > > > > You would need this to match your description: > > > > HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > > > > > select companyId, departmentId,employeeId, employeeFirstName, > > > > employeeLastName max(payDate) from employeeLog where payDate=(select > > > > top(1) payDate from employeeLog where payDate< 20100101 order by > > > > payDate desc) > > > > > I'm guessing the one with the group bys would be faster, or maybe > > > > there's another way to do it? > > > > The only way to find out is to test. And then you only know what > > > was best for that set of data. > > > > But if I understand the description of what you are asking for correctly, > > > the second query seems more straightforward. > > > > -- > > > Erland Sommarskog, SQL Server MVP, esq...(a)sommarskog.se > > > > Links for SQL Server Books Online: > > > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > > > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > > > SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books..mspx > > > Actually the query does work without the > > HAVING max(payDate) = (SELECT MAX(payDate) WHERE payDate < '2010010') > > > because of the group bys. I'll test it out to see which is faster, > > but I was just wondering if there was a general rule when it comes to > > this.- Hide quoted text - > > > - Show quoted text - > > Testing it I found the bottom one almost twice as fast, and I'm pretty > sure my query is correct. If I change max to min, I get different > results. > Also, notice I don't have payDate in the groupBy, otherwise it will > return more than 1 date. > > select companyId, departmentId,employeeId, employeeFirstName, > employeeLastName max(payDate) from employeeLog where payDate< > 20100101 > and companyId=10 and departmentId=50 group by companyId, > departmentId,employeeId, employeeFirstName, employeeLastName- Hide quoted text - > > - Show quoted text - Just to clarify the data I want back. Given Data In the table companyID, employee, payDate 1, Joe, 2007 1, Bob, 2007 1, Joe, 2008 1, Bob, 2008 2, Mary, 2008 2, Mary, 2009 1, Joe, 2010 1, Bob, 2010 I want to pass in 2010 and get the people in company 1, the first date before 2010 This is what I want back (2 Rows): 1, Joe, 2008 1, Bob, 2008
|
Next
|
Last
Pages: 1 2 Prev: New Instance Installation Failure Next: Replace no init backup with init backup? |