November 20, 2003 at 8:42 am
I am trying to create a view that will limit the results to 10000 with a TOP clause since it will possible return 1000000 row without a filter. The problem comes in when the user will filter the view with a Where clause to get the results they want.
I think what it is doing is taking the first 10000 records and then filtering it, which isn't helping my cause. Order By seems to change the order but will still bring back the first 10000 then filter.
Is there any way around this or can you make a view filter before it sorts?
All help will be appreciated.
November 20, 2003 at 3:20 pm
I'm unaware of a way to do that, unless the filter is also part of the view definition.
You could make it a stored procedure or UDF that returns a result set, but then you have to specify which things they can filter by, or use one of the two string execute methods and build the query.
While I have never used it, perhaps something along this line would help (from sql help):
query governor cost limit Option
Use the query governor cost limit option to specify an upper limit for the time in which a query can run. Query cost refers to the estimated elapsed time, in seconds, required to execute a query on a specific hardware configuration.
If you specify a nonzero, nonnegative value, the query governor disallows execution of any query that has an estimated cost exceeding that value. Specifying 0 (the default) for this option turns off the query governor. In this case, all queries are allowed to run.
If you use sp_configure to change the value of query governor cost limit, the changed value is server-wide. To change the value on a per connection basis, use the SET QUERY_GOVERNOR_COST_LIMIT statement.
query governor cost limit is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change query governor cost limit only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and restart).
November 21, 2003 at 9:50 am
I would use UDF. UDFs are very similar to views in the sense that you can use a UDF as part of a SELECT statement and yet more powerful in the sense that it allows to so filter stuff.
You can select top 10000 records in UDF and if you like sort it and later do further processing on the resulting 10000 records.
e.g.
SELECT TOP 10000 * from mytable1 mt1
inner join dbo.MyUDF (parameteres if any) mt2
where mt1 condition and mt2 condition
--Ibrahim
November 21, 2003 at 9:52 am
I forgot to mention that UDFs work only on SQL Server 2000.
November 25, 2003 at 9:26 am
alatoum, I like your idea, but I am still forced to hardcode an Order By column in the UDF. The problem with that is the user cannot then filter on any column he wants because the return table from the UDF will only contain the top 10000 based on the hardcoded Order By column.
It's like I need to let the user do the Filter and Sort and then take the top 10000. I want to do this because a select on the view without a good filter will bring back a million rows. I don't know that this can be done with SQL 2000 in a view.
Any other suggestions?
November 25, 2003 at 12:18 pm
If prepared sql/string executes wont fit the bill, how about:
set rowcount 10000
<do query>
set rowcount 0
Or if you have to have this logic in a view/udf instead of a stored procedure how about using case statements and including logic for usage of any or all fields in a UDF? The below example runs in northwind. It allows matching of any, all, or no fields in the customers table. Although it could bear specific case-by-case testing,
bypassing references to fields by using case statements has allowed me to avoid large overhead on 'search it all' procedures in the past.
Having it in the UDF also allows you to get only the top 10k. Using case to also sort the results, based on the filtering criteria, allows for dynamic/automatic sorting before the top clause is applied.
still, it'd be nice if you could get more specific functionality requirements so you didn't have to have a 'do anything' type sp.
November 25, 2003 at 12:23 pm
go
create function dbo.tfnCustomerQuery(
@CustomerID nchar(5)
,@CompanyName nvarchar(40)
,@ContactName nvarchar(30)
,@ContactTitle nvarchar(30)
,@Address nvarchar(60)
,@City nvarchar(15)
,@Region nvarchar(15)
,@PostalCode nvarchar(10)
,@Country nvarchar(15)
,@Phone nvarchar(24)
,@Fax nvarchar(24)
)
returns @Customers table (
CustomerID nchar(5) not null
,CompanyName nvarchar(40) not null
,ContactName nvarchar(30) null
,ContactTitle nvarchar(30) null
,Address nvarchar(60) null
,City nvarchar(15) null
,Region nvarchar(15) null
,PostalCode nvarchar(10) null
,Country nvarchar(15) null
,Phone nvarchar(24) null
,Fax nvarchar(24) null
)
as
begin
insert into @Customers
select top 10000 * from Customers
where
1 = case when @CustomerID is null then 1 else
case when CustomerID like @CustomerID+'%' then 1 else 0 end end
and 1 = case when @CompanyName is null then 1 else
case when CompanyName like @CompanyName+'%' then 1 else 0 end end
and 1 = case when @ContactName is null then 1 else
case when ContactName like @ContactName+'%' then 1 else 0 end end
and 1 = case when @ContactTitle is null then 1 else
case when ContactTitle like @ContactTitle+'%' then 1 else 0 end end
and 1 = case when @Address is null then 1 else
case when Address like @Address+'%' then 1 else 0 end end
and 1 = case when @City is null then 1 else
case when City like @City+'%' then 1 else 0 end end
and 1 = case when @Region is null then 1 else
case when Region like @Region+'%' then 1 else 0 end end
and 1 = case when @PostalCode is null then 1 else
case when PostalCode like @PostalCode+'%' then 1 else 0 end end
and 1 = case when @Country is null then 1 else
case when Country like @Country+'%' then 1 else 0 end end
and 1 = case when @Phone is null then 1 else
case when Phone like @Phone+'%' then 1 else 0 end end
and 1 = case when @Fax is null then 1 else
case when Fax like @Fax+'%' then 1 else 0 end end
order by
case when @CompanyName is null then '' else CompanyName end
,case when @ContactName is null then '' else ContactName end
,case when @ContactTitle is null then '' else ContactTitle end
,case when @Address is null then '' else Address end
,case when @City is null then '' else City end
,case when @Region is null then '' else Region end
,case when @PostalCode is null then '' else PostalCode end
,case when @Country is null then '' else Country end
,case when @Phone is null then '' else Phone end
,case when @Fax is null then '' else Fax end
,CustomerID
return
end
go
-- select all customers (default sorts by customerid, limits to 10k)
select * from dbo.tfnCustomerQuery(null, null, null, null, null, null, null, null, null, null, null)
-- select all customers whose company name starts with b
select * from dbo.tfnCustomerQuery(null, 'b', null, null, null, null, null, null, null, null, null)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply