hi Jakub
try using  a as a 'SET ROWCOUNT a', prior to the select
statement.
r
---------- Original Message
----------------------------------
From: "Poskrobko, Jakub, (CERI)"
<Jakub.Poskrobkoceri.pl>
Reply-To: aspnet-databasesaspadvice.com
Date: Tue, 28 Nov 2006 08:24:53 +0100
>Hello 
>
>I want to run query like ' select top a id from
trans ' , where a is
>external parameter of the query;
>And i _dont_ want to make it like exec ('select top ' +
a +
' id from
>trans').
>
>So i created query:
>
>
>SELECT id
>FROM trans tz
>WHERE a >
>(
> SELECT
> count (tw.id)
> FROM trans tw
> WHERE tw.id < tz.id
>)
>
>And it work fine (about half shorter than 'exec'); but
only until a <
>229; for a >= 229, 'exec' is about 4 times
quicker...
>
>I suppose that happens because Nested Loop that
optimizer creates; and i
>suppose that hash join would be faster. Is there any
method to 'hint'
>optimizer to use hash join when no join-clause is
specified?
>
>Or maybe anyone has an idea, why does it happen and how
to resolve it?
>
>Table 'trans' contains about 3300 rows, id field is int
and cluestered
>indexed; OPTION (FAST 229) works great - but on a = 300
both queries
>times are equal...
>
>Thanks for any suggestion how to parametrize 'top'
clause without string
>concatenation...
>
>Kuba Poskrobko
>
>
>Need SQL Advice? http://sqladvice.com
>Need RegEx Advice? http://regexadvice.com
>Need XML Advice? http://xmladvice.com
>
____________________________________________________________
____
Sent via the WebMail system at ebiz-developer.com
Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com
|