creating such a huge query that the server can't handle it. The IN section
in some cases are so large that I can't even troubleshoot it in Query
Analyzer because it's larger than 64k. Is there another way to write:
SELECT t1.col1, t1.col2
FROM table1 t1
WHERE EXISTS
(
SELECT t2.col1, t2.col2
FROM table2 t2
WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
list items here>
)
_____
DC GWHy dont you store them in a table ? Would be much easier to maintain, you
ould additionaly activate / deactivate some entries here:
Create Tabel MyValues
(
MyValue varchar(200),
Acitvated bit
)
SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN
(Select * from myVales where Activated =1 )
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--
"DC Gringo" <dcgringo@.visiontechnology.net> schrieb im Newsbeitrag
news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>I have a series of select queries that use the nesting method but are
>creating such a huge query that the server can't handle it. The IN section
>in some cases are so large that I can't even troubleshoot it in Query
>Analyzer because it's larger than 64k. Is there another way to write:
> SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
> list items here>
> )
> _____
> DC G
Create a table with the values (one column, multiple rows) and do a subquery inside your IN (or
EXISTS).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>I have a series of select queries that use the nesting method but are creating such a huge query
>that the server can't handle it. The IN section in some cases are so large that I can't even
>troubleshoot it in Query Analyzer because it's larger than 64k. Is there another way to write:
> SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of list items here>
> )
> _____
> DC G
First of all, no need to post a question to all the groups in your
subscription list.
You should have posted only to microsoft.public.sqlserver.programming
How is the list being passed into the query?
Take a look at:
http://vyaskn.tripod.com/passing_ar..._procedures.htm
It may give you a few ideas on how to deal with this.
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>I have a series of select queries that use the nesting method but are
>creating such a huge query that the server can't handle it. The IN section
>in some cases are so large that I can't even troubleshoot it in Query
>Analyzer because it's larger than 64k. Is there another way to write:
> SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
> list items here>
> )
> _____
> DC G
DC Gringo,
The example posted is not using a correlated subquery, the is not a relation
between table1 and table2. Idf it is correct, then you can rewrite it as:
if exists(select * from table2 where col2 in (...))
select col1, col2 from table1
if it was a typo error, then you can create a temporary table to insert the
values in the list, with an associated index and use:
create table #t (c1 int)
insert into #t values(...)
...
create nonclustered index ix_#t_c1 on #t(c1)
SELECT
t1.col1, t1.col2
FROM
table1 as t1
inner join
(
select
t2.col1
from
table2 as t2
inner join
#t
on #t.c1 = t2.col1
) as t3
on t1.col1 = t3.col1
drop table #t
go
AMB
"DC Gringo" wrote:
> I have a series of select queries that use the nesting method but are
> creating such a huge query that the server can't handle it. The IN section
> in some cases are so large that I can't even troubleshoot it in Query
> Analyzer because it's larger than 64k. Is there another way to write:
> SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
> list items here>
> )
> _____
> DC G
>
>
Raymond, frankly I didn't expect a SQL T-sql answer. I was expecting a
vb.NET answer...but figured I'd try both...I am passing it in via raw SQL
from a .vb component. It is a query building application.
_____
DC G
"Raymond D'Anjou" <rdanjou@.savantsoftNOSPAM.net> wrote in message
news:uTm7J%23%23TFHA.952@.TK2MSFTNGP10.phx.gbl...
> First of all, no need to post a question to all the groups in your
> subscription list.
> You should have posted only to microsoft.public.sqlserver.programming
> How is the list being passed into the query?
> Take a look at:
> http://vyaskn.tripod.com/passing_ar..._procedures.htm
> It may give you a few ideas on how to deal with this.
> "DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
> news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>>I have a series of select queries that use the nesting method but are
>>creating such a huge query that the server can't handle it. The IN
>>section in some cases are so large that I can't even troubleshoot it in
>>Query Analyzer because it's larger than 64k. Is there another way to
>>write:
>>
>> SELECT t1.col1, t1.col2
>> FROM table1 t1
>> WHERE EXISTS
>> (
>> SELECT t2.col1, t2.col2
>> FROM table2 t2
>> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
>> list items here>
>> )
>>
>> _____
>> DC G
>>
>>
>>
Hey DC,
If you have rights to create tables temprorarily, I'd agree with most of the
other posts. A few other ideas follow. Also, can you post more specifics
about what sort of things can be part of the IN clause?
- Are there any patterns you can narrow pieces down to, e.g., CAST( t2.col2
AS int) BETWEEN 1 AND 42?
- Maybe you could use the query to narrow the majority of your data and
either manipulate it on the .NET side or use a DataView to handle the rest
of the trimming.
- Can the logic be "reversed", e.g., if you can have char representations of
numbers ranging '1' to '10000', use NOT IN and supply a smaller set of
non-valid options.
HTH,
John
"DC Gringo" <dcgringo@.visiontechnology.net> wrote in message
news:e9nOtz%23TFHA.580@.TK2MSFTNGP15.phx.gbl...
>I have a series of select queries that use the nesting method but are
>creating such a huge query that the server can't handle it. The IN section
>in some cases are so large that I can't even troubleshoot it in Query
>Analyzer because it's larger than 64k. Is there another way to write:
> SELECT t1.col1, t1.col2
> FROM table1 t1
> WHERE EXISTS
> (
> SELECT t2.col1, t2.col2
> FROM table2 t2
> WHERE t2.col2 IN ('1','2','3','4'...<there could be thousands of
> list items here>
> )
> _____
> DC G
learn_asp_fast: