May 7, 2002 at 7:55 am
Hi Gurus,
I have an sql query in which I am using the TOP operator in the select clause in the main query and in the subquery. I need to make the value of TOP operator as a parameter. For simplicity consider the query
select top 10 * from Employees
In the above query the value 10 must be a parameter like
select TOP @MyCount * from Employees
I can pass @MyCount parameter value to the stored procedure where this query is executed. I am looking for a solution in which dynamic sql is not used (using exec) and no temporary tables are involved. This is because of performance reasons.
Any help on this would be greatly appreciated.
S.Mohamed Yousuff
May 7, 2002 at 8:20 am
You cannot do this with SELECT TOP x but try you can use SET ROWCOUNT to get the same result. See BOL for more details.
Ex.
DECLARE @ret int
SET @ret = 10
SET ROWCOUNT @ret
SELECT * FROM tblX
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 8:40 am
Thanks for the information. As I mentioned in the problem, I am using the TOP operator in both the main query and the sub query. Hence ROWCOUNT is not of help in this specific case.
S.Mohamed Yousuff
quote:
You cannot do this with SELECT TOP x but try you can use SET ROWCOUNT to get the same result. See BOL for more details.Ex.
DECLARE @ret int
SET @ret = 10
SET ROWCOUNT @ret
SELECT * FROM tblX
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 10:46 am
I see, can you post what you have currently done and I will see what I can come up with?
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 7, 2002 at 10:56 am
A technique pretty similar would be to use set rowcount to create a temp table with the x number of rows, then include the temp table in your query - using it to limit the number of rows returned.
Andy
May 8, 2002 at 4:06 am
The problem is related to record paging using sqlserver. I am using .NET to access sqlserver. There is no concept of connected recordset with sqlserver in .NET which means no server side cursors. For simplicity I will explain my problem through the traditional Employees table.
I have a employees table with large number of records say 500,000 records. (Considering the number of records expected, usage of temp table may not be a good solution). There is no identity column in my table. While paging the records I may sort it based on any column. For a given page number and page size and sort order, only those records should be pulled from the server. I prefer not to use any dynamic sql or temp tables. Dynamic sql may solve the problem with some performance degradation but temp tables is not acceptable due to the large number of records. The query to achieve paging is like
select TOP 10 * from Employees where EmployeeId not in ( select TOP 20 EmployeeId from Employees)
In the above query 10 is the number of records to retrieve (page size) and 20 is the starting position of the record. The starting position is calculated by first issuing a recordcount query prior to issuing the above query and dividing the record count by the page size.
Just executing the query after building it dynamically solves the problem but this degrades the performance due to recompilation by sqlserver. Hence I am looking for a better solution
S.Mohamed Yousuff
quote:
I see, can you post what you have currently done and I will see what I can come up with?"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 5:05 am
Try something like this is the only way I see to do it without a temp table.
Note the where clause on the subquery to get the records up to a certan count, you will need to use a row with unique values for this to work best. Also other where criteria for the suquery should be applied inside the inner most subquery.
Also not sure exactly what the impact will be on extremly large @subrows values.
DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (
SELECT EmployeeId from Employees ol WHERE (SELECT COUNT(*) FROM Employees il WHERE il.EmployeeId < ol.EmployeeId) < @subrows
)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 5:20 am
Depending on your final goal, it might make sense to continue to use ADO for this rather than ADO.Net.
Andy
May 8, 2002 at 5:32 am
I tested the query with just 15000 records. The performance is not to an acceptable level. Processor stays 100% during the entire duration of the execution of the query. I/O Read shows 1852243 (too high) and the total duration is 47.970 seconds (not milliseconds).
quote:
Try something like this is the only way I see to do it without a temp table.Note the where clause on the subquery to get the records up to a certan count, you will need to use a row with unique values for this to work best. Also other where criteria for the suquery should be applied inside the inner most subquery.
Also not sure exactly what the impact will be on extremly large @subrows values.
DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (
SELECT EmployeeId from Employees ol WHERE (SELECT COUNT(*) FROM Employees il WHERE il.EmployeeId < ol.EmployeeId) < @subrows
)
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Edited by - mohamedyousuff@yahoo.com on 05/08/2002 05:33:09 AM
May 8, 2002 at 7:04 am
That is what I was afraid of. Then your next best option would be with a temp table, which should work best if you limit to the data you need by doing like so.
DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
CREATE #tmpEmp (
[EmpID] [int]
)
SET ROWCOUNT @subrows
INSERT INTO #tmpEmp (EmpID) SELECT EmployeeId FROM Employees
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (SELECT EmpID FROM #tmpEmp)
DROP TABLE #tmpEmp
Beyond that it will become an index game to speed either soultion up if even possible.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 8, 2002 at 11:54 pm
Thanks Antares686. That was a great help from you. The query is working fine. I tested with 15000 records. Seems when I retrieve records from the top i.e. say from record number 200 to 250, the records appear within 33 milliseconds (cpu 32, IO read is 2281, write is 1) but as it goes down the lane say from 14900 to 14910 ( one of the worst case ), the duration is 216 milliseconds, cpu 219, IO Read is 862 and write is 24. Performance seems to reduce as we retrieve pages from top to bottom. Still I think 216 milliseconds duration is reasonable. What do you think about it ?
S.Mohamed Yousuff
quote:
That is what I was afraid of. Then your next best option would be with a temp table, which should work best if you limit to the data you need by doing like so.DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
CREATE #tmpEmp (
[EmpID] [int]
)
SET ROWCOUNT @subrows
INSERT INTO #tmpEmp (EmpID) SELECT EmployeeId FROM Employees
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (SELECT EmpID FROM #tmpEmp)
DROP TABLE #tmpEmp
Beyond that it will become an index game to speed either soultion up if even possible.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 4:59 am
Personally my goal it to limit most queries to 180- seconds for any major query run, if it cannot be done in that timeframe as most of the major queries are for reporting purposes then I either reevent the query in some other way to decrease time or run as a batch at night to output.
Otherwise all queries should be under 30 seconds. You said this was 216 milliseconds, are you sure, if so then I see not issue at all. If you meant seconds the only other thing I could suggest is the following. Create a ranking table which is updated external to inserts or via trigger on insert, update and delete. By having this table already in existance and updated by changes in the main table you can easily have an ID field that you keep in line so that you can run a subquery directly against it where the ID <= to your subquery variable. I use this method on a site where they want the next record functionality similar to Access on the data pages as I did not want to hold a cursor with the amount of data in question, they have the ability to flow from one record to the next forward and backwards and to type a specific number position in to jump to. This would be the only way I can see that would help, beyond that not anything else I can see.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
May 9, 2002 at 10:45 am
Mohamed,
I'd be interested to know how fast it is if you use a table variable instead of a temporary table (assuming you can)...
DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
declare @EmpIDs table (EmpID int)
SET ROWCOUNT @subrows
INSERT INTO @EmpIDs (EmpID) SELECT EmployeeId FROM Employees
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (SELECT EmpID FROM @EmpIDs)
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
May 11, 2002 at 6:14 am
Hi RyanRandall,
This approach does not seem to work well. For the top records ( records 10 to 15 ), performance is good. CPU - 16, IO Read 233, Duration is 16 milliseconds. Now moving down slightly to retrieve records 1000 to 1005, the CPU is 594, IO Read is 8640, Duration is 610 milliseconds. Seems to degrade the performance very fast as we move from top to bottom. Hence I tried again to retrieve records 5000 to 5005. Now CPU 13797, IO Read 95684, IO Write 1, Duration 15.343 SECONDS (not milliseconds).
I think the reason for performance problem with this approach is that the table variable does not have indexing on it whereas the temporary table has.
S.Mohamed Yousuff
quote:
Mohamed,I'd be interested to know how fast it is if you use a table variable instead of a temporary table (assuming you can)...
DECLARE @rows int
DECLARE @subrows int
SET @rows = 12
SET @subrows = 10
declare @EmpIDs table (EmpID int)
SET ROWCOUNT @subrows
INSERT INTO @EmpIDs (EmpID) SELECT EmployeeId FROM Employees
SET ROWCOUNT @rows
SELECT * FROM Employees WHERE EmployeeMgr IN (SELECT EmpID FROM @EmpIDs)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply