• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 585
  • Last Modified:

sql select with where condition being too long

I am querying a db2 db via odbc and my query is including a where condition which is too long (15k names).
is there a way to rephrase the query to get the driver to accept this long list.
current query is:
select sknum, skcli, sktit, skdatc, skmont, skart, skaf, skqte, skgenr from srl where (skgenr = 'A' or skgenr = 'V')
 and (skdatc between 20070101 and 20100331)
 and (skcli='xxxxx' or skcli='yyyyyyyyy' or ..... 15000 names ......... or skcli='zzzzzzz' )


0
lesouef
Asked:
lesouef
  • 4
  • 2
  • 2
  • +3
4 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi lesouef,

You list 15,000 separate conditions on a WHERE clause?  All I can say is "wow"....

You'll find it hugely more efficient if you'll put those names into a table and inner join the table to your query.  If you want the exception rows, outer join the table to your query and select the rows where the value is NULL.


Kent
0
 
whowho12Commented:
Where are you getting these 15k names from? Are these names in another table? If they are not, could you feed them to a temporary table?

If they are in another table then the simplest solution would be to do a JOIN between the two tables.

Something on the lines of:

SELECT mynames.skcli, srl.sknum, srl.sktit, srl.skdatc, srl.skmont, srl.skart, srl.skaf, srl.skqte, srl.skgenr
FROM mynames
LEFT JOIN srl ON mynames.skcli=srl.skcli
WHERE (skgenr = 'A' or skgenr = 'V') AND (skdatc between 20070101 and 20100331)
0
 
Dave FordSoftware Developer / Database AdministratorCommented:

.... alternately, you could use the IN clause to simplify the expression

d.g.

(skcli in ('xxxxx', 'yyyyyyyyy', 'zzzzzzz' )

HTH,
DaveSlash
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
_agx_Commented:
(no points)

Yes, I agree an IN clause is more elegant. But obviously it too has limits.  15k is simply too many items. So IMO the first two suggestions of re-factoring are the best solution here.
0
 
Anthony PerkinsCommented:
>>Zones: DB2 Database, MS SQL Server, MySQL Server<<
Pray tell what has this question got to do with MS SQL Server or MySQL Server for that matter?
0
 
lesouefAuthor Commented:
to answer all these:
- I have also tried IN ('xxxx', 'xxxx'...) a bit faster, but not a big change. I do it now but batches of 100 at a time to work around the limits.
- where this list come from: a table, yes, but from another db on another system, and I can't make a tmp table in db2 as I am read-only on this main db. so I read it from the 2nd server and use this as arguments to query the 1st server. weird world, but this is the result of a badly managed company.
 - and is it in MSSQL because the list source is on a MSSQL server, but yes this is not the problem. actually the question is more a generic SQL question, but I did not find any software category like that.
0
 
_agx_Commented:
> I do it now but batches of 100 at a time to work around the limits.

   If you cannot create a temp table, or access the other db directly in your query (like a linked server in
   MS SQL).. it sounds like that's the best you can do.
0
 
lesouefAuthor Commented:
no idea, I have no as400/db2 admin here, and I am totally db2 ignorant myself, so I have to work 'as is', this machine being on its way out...
0
 
Anthony PerkinsCommented:
>>actually the question is more a generic SQL question<<
Then the "generic" solution is to build a table to contain all the "skcli".  Add all 15K "skcli" values to the table and then do an INNER JOIN using the DB2 SQL syntax.
0
 
lesouefAuthor Commented:
I thought of doing this, using a linked server from mssql to db2. but this requires to install an ODBC driver on mssql server to mount the db2 database as a linked server and ends up as being as slow as my solution. anyway, I will probably copy the entire table on a local db overnight and do these stats locallly.
0
 
lesouefAuthor Commented:
the linked server, apart from the speed is probably the most logic, even better than making a temp table, since being instantly available and using for any other crossed query.
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now