substitute for nested select query

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 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

substitute for nested select query

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 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 creat
ing 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 a
nother 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 l
ist 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 sectio
n
> 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...
>
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
>
>

Suggestion needed for huge DataGrid

Hi all:
Our web site uses 3-tier structure, the middle tier is a web service. We
also use Microsoft Data Access Application Block to access sql server.
Now I have to display a DataGrid with huge amount of data on web page,
of course the DataGrid has pager. But even though, the web service still
will return full DataSet to web server, and let web server to handle pages.
Since Microsoft Data Access Application Block does not support
SqlDataAdapter.Fill(DataSet, startReocrd, maxRecord, tableName) to reduce
the size of DataSet. Do you think there is a better way to improve
performace?
Yes, I can always extend Microsoft Data Access Application Block and
build my own pager.

--
WWW: http://hardywang.1accesshost.com
ICQ: 3359839
yours HardyHi Hardy,

May I suggest two solutions:
-if the full set of lines is going to be requested sooner or later, fill the
DataSet and place it in the Cache for the first time, then read it from the
Cache.
-if you don't need the full set of lines, you may use the SqlDataReader
combined with a TOP N command in your SQL query.
--
To reply, remove a "l" before the @. sign.

Arnaud Weil - MCT, MCSD.Net, MCAD.Net

Suggestions to build Newsletter

Hi.

I want to build Newsletter to about 100 thousand user.

what is the best way to send that huge E-mails .

I think to use Thread.Sleep(500) after evry mail , is that good way , is there any more professional ways?

Good Evening Kojoh,

There are multiple was to do it, How I do it, is store the email addresses in a database, sql server 2005 but you can use the express version if you so choose, then you can either use sql to send the email(I don't think you can do this with the express version) but you can use asp.net to do it. You can just create a list and then send it via system.net.mail or if you have access to your own mail server, create a distribution list based on the addresses stored in the database. Totally up to you. Not sure your requirements or utilities at your disposure.

Joshua

thank's dearfolkertsj .

I don't ask how to send mail , I know how to do it . but my question is if I use to send thousands of mails one time it will hung the server.

Regards.

Indeed - the issue is how to send bulk emails.

There are lots of ways to achieve this safely. I find the simplest and easiest way is to use a bulk email component. The one I typically use for this isAspEmail (works with ASP and ASP.NET) as it has support for just about everything you can think of.

DearBSolveIT Thank's for your replay ,

I know the code and I have host server that support SMTP Server . I wl explain more:

I use like this code:

while (ReaderUsers.Read()) { MailAddress To =new MailAddress(ReaderUsers["UserMail"].ToString()); MailAddress From =new MailAddress("Email@.Email.com"); MailMessage MyMail =new MailMessage(From, To); MyMail.Subject ="Subject"; MyMail.Body ="the body"; SmtpClient smtp =new SmtpClient("my smtp Server"); smtp.Send(MyMail); }

This Reader contain a thousands of recored . if I send it like this I will make a problems in the server I gess. how can solve this problem , is it good done if I useSystem.Threading.Thread.Sleep() between E-Mails?

Hi Kojoh

I would suggest that you probably don't want to do that. I understand that you want to slow down the speed at which the emails are generated, but then you're also going to end up with a page that will take a LONG time to process... which you'll then have to cater for.

Better, as I mentioned, is to use a bulk email component. The one I mentioned uses it's own SMTP server, and handles it's own queue. However, it has 64 SMTP threads allowing it to send hundreds of emails a second.

If you don't have your own server, ask your hosting company if there is a bulk email capable component installed on the server that you can use. if they haven't got one, ask if they would consider installing the one I suggested.

BSolveIT:

If you don't have your own server, ask your hosting company if there is a bulk email capable component installed on the server that you can use. if they haven't got one, ask if they would consider installing the one I suggested.

Thank's alotBSolveIT.

my host need extra money to do that, now I look to do that by the code!! is there any suggestions ?.

up.

I strongly advise to switch to a host that has support for this, as either way you're current host is likely to be unhappy.