SQL Looping for Complex Query

SQL Looping for Complex Query

• Sergey Romanov

  If what your colleague meant is to loop through multiple nested ADO recordsets in ASP, I would tend to disagree unless there is a specific reason to do so. In my experience, when you are pulling a straight relational dataset without any high end calculations, performance of a query fully contained in the DB environment will always out perform a programmatically accessed recordset. After LINK hits, that may not be the case anymore, but I think it is for now.

  Again, without more details it is difficult to say if that is your situation.

  Hope that helps,
  Hugh

  
  

• hornet777

  Well, that doesn't appear to be all that complex...and, it's only requesting data from what appears to be a single table (what you're calling B in this case)...

  Could you possibly take the query as it is and post it, replacing table names with fake names and values with fake values If the query is structured like this one, I don't know how much simpler it could get...

  
  
  

• Colin426

  It's pulling multiple pieces of info about a product fom multiple tables... best way to describe is:

   

  SELECT

  FROM

  WHERE

  I don't think anything is a view... but basically this is the structure.  I'm going to see if I am allowed to share further information.  I'm likely not, but I understand the need to see.  Let me express my sincere appreciation of your willingness to help, sir.

   

   

   

  
  

• cislom

  Damn I was sad to see this never got answered. I am looking to use a loop in a select query. I have a table with all the cities, states and counties in the US along with other colums. I want to split the table into multiple tables CITY STATE COUNTY each with its own ID along with the name. What I want to do is use a loop to iterate through each state and pick out the distinct counties in each, same thing with the cities...... Any help would be appreciated

  Here is what I've done so far

  Insert Into ZipCodesSQL.dbo.States(State)

  Select Distinct State

  From ZipCodesSQL.dbo.ZipCodesSQL

  Order By State Asc

  Thanks

  Ken

  Evansville Indiana

  
  

• Hua Wang

  Thanks csi_hugh!

  For security reasons, I am not allowed to provide an exact copy. Sorry about that.

  But here is a rough sketch of a query that requests multiple pieces of information from multiple tables.

  SELECT A

  FROM B

  WHERE C=D and D=1

  I apologize that I cannot provide more information tahn this. If you have any wroking samples or know of some links, taht may help as well.

  Many Thanks!

  travelerkgd

  
  

• Pi in the Sky

  Thanks Hugh... I see what you're saying.  It's a tough fix indeed.  If it's still slow, the Stack Compile/Timeout error may still well appear.  If I were allowed to show more, no doubt the problem would be closer to fixed.  Will just have to keep after it.
  

• tightcoder

  Ok, now we're getting somewhere...the query above is much different than the one in the outline further up...in this query, you are selecting from 3 different objects: location, style, and price_range...

  First off, how are these 3 (or more) objects being joined In the most recent query, there is no join condition anywhere, which would result in a cross-join amoung each of the objects...which would be very bad for performance.

  If you can't post the original query, which I understand by the way, could you post a fake query that matches the structure of the original query, something like this:

  If real query is:

  select cars.*, trucks.*, vans.*

  from cars, trucks, vans

  where cars.status = 'new' and trucks.status = 'new' and vans.status = 'new'

  Post a query like this:

  select tableA.*, tableB.*, tableC.*

  from tableA, tableB, tableC

  where tableA.colA = 'new' and tableB.colA = 'new' and tableC.colC = 'new'

  If you could do something even like that, it would help a lot...

  HTH,

  
  
  

• hashmi

  That pretty much sums it up... but I double checked and I'm not allowed to post anything closer to what's displayed now.  However, a senior level colleague suggested an ASP loop for the SQL statement - can I ask how this might be done, in general terms Thanks for all the patience.
  

• Elona Tuomi MSFT

  I'm really not allowed, could get into lots of trouble... but it's basically that simple :(

  
  

• Boris L

  travelerkgd, if you can provide some more detail or even a copy of the query, that will make it easier to see some solutions. There are many varieties of complex queries, so without detail it is hard to say what would benifit your query.
  

• Stuart_D

  Well I'm sorry I didn't let anyone get a chance to answer my question but I figured it out myself.

  All Tables are populated and life is good.

  Insert Into ZipCodesSQL.dbo.Counties(County,StateID)
  Select Distinct County , StateID
  From ZipCodesSQL inner join States
  On ZipCodesSQL.State = States.State
  Order BY County ASC

  Cool Forum

  Regards

  Ken

  Evansville Indiana

  
  

• Bowties

  Well, I don't see how you could simplify that query much at all, it's as basic as it gets.

  You mentioned the query pulls and gathers information from multiple tables, but the outline above is only pulling from a single table, so where do the multiple tables come from Is 'B' a view Or are there embedded 'select' statements inside the column select list (i.e. 'A') Is 'B' a sub-select statement We're going to need more information to help at all...the only way I can see the above statement being in-efficient at all is if table 'B' has about 10 million rows and the filter is non-selective...

  
  
  
• SQL Looping for Complex Query