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

Limit List in Query

Experts-

I have a very large query and would like a better way to run it.  

I have a query that is pulling 50 accountID's and based on those accountID's it pulls data for thousands and thousands of campaign for each accountID.  It tends to crash the server because the query runs for a very long time to gather all of this information.  If I run 5 accounts at a time, it seems to run ok.

So my question is, what would be the best way for me to run 5 accountID's at a time?  The initial query returns 50 accountID

SELECT Account_ID, Account_Status, Account_Name, API_Vendor, ReferenceId
                  FROM API_Accounts
where referenceID IN('6250714423','4713441501','8946672149','2266698677','3483382931')

Any suggestions would be greatly appreciated
0
nmarano
Asked:
nmarano
  • 4
  • 4
  • 3
  • +1
3 Solutions
 
Nathan RileyFounder/CTOCommented:
Is the table indexed?
0
 
nmaranoAuthor Commented:
It is, but it's used during an API pull from another system.  So in pulling all of these campaigns from the other system, their seems to be a slow-down.  So if I can loop through 5 of the accounts at a time, it would be beneficial

-nick
0
 
gdemariaCommented:
Nick, I'm wondering what you are doing with the data after you pull it?   I would guess you are not attempting to display it to the screen; there is too much of it, right?

I think depending on where the information is ending up, may help answer the question.  Is it being written into a file or imported into another database?

Thanks
0
New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

 
Angelp1ayCommented:
If you have SQL Server 2012 it's quite easy:
http://msdn.microsoft.com/en-us/library/ms188385(SQL.110).aspx
SELECT Account_ID, Account_Status, Account_Name, API_Vendor, ReferenceId
FROM API_Accounts
ORDER BY referenceID ASC 
OFFSET 15 ROWS 
FETCH NEXT 5 ROWS ONLY 

Open in new window

...for each query keep increasing the offset...
0
 
nmaranoAuthor Commented:
Hey Maria-

It does get displayed.  Essentially this is a API pull from Google Campaigns.  So we have an accountID which is the Google AccountID.  Within the Google AccountID, there are thousands and thousands of campaigns (campaignID's).  Once all of the data(Google Spend) is downloaded into our DB, we display different calculations on what was returned.  

Does that make sense?

Angelp1ay- For each query keep increasing the offset?  I don't want to write 5 queries.

Thanks
Nick
0
 
Angelp1ayCommented:
Do you just want one query that slowly loops over a very long time?

If not and you need to call it repeatedly, you need some mechanism to identify what is already done.

One option might be to include in your package something to log the last account id processed and then you can make your select query in function of this data. If you can be sure that every time you query you get the same order (e.g. if insertions to this table are keyed by date and you order over this) then you could instead log the last row number (and again include this as a variable in your query).

Since I imagine at any point new data could be added anywhere in the source table, and since your extraction process is not instant, how will you ensure you don't extract an account which is then updated after your pass but before the end of the overall run? Probably the best would if it's possible to extract for a past date range (so you have a defined end date and you already know all the data is in for that range).
0
 
gdemariaCommented:
> Once all of the data(Google Spend) is downloaded into our DB, we display different calculations on what was returned.  

I assume this is the part that you're having trouble with.   The pull from Google.   I think it's essential that you keep track of which records have been downloaded and not; if the system is interrupted, you don't want to start over.   Can you pull them over in order of ID, then you can pull based on the last ID you've downloaded the previous time?
0
 
Angelp1ayCommented:
> ...then you can pull based on the last ID you've downloaded the previous time?
This assumes the data associated with each ID isn't changing in the mean time.

Since these are account IDs I would expect that the data associated continues to change. I think you need more than just the account id, hence why I suggested setting a reporting date (some time in the past) and downloading only data up to that date. This of course requires the data to be bound to something representing time e.g. transaction date, incremental transaction id, ...
0
 
nmaranoAuthor Commented:
Hello Experts-

Sorry for the delay in my response.  Yes the pull is done by dates and by accountIDs....Those accountIDs tell Google to gather all of the related campaigns associated with that accountID and pull all of the spend, clicks, cost per click etc etc...

An easy, but not efficient solution is for me to set up 5 scheduled tasks in CF admin.  Within the task, I can set a url var 1, 2, 3, etc and have those URL vars call specific accountIDs which would then run the code....Again, an easy, but not efficient fix...
0
 
gdemariaCommented:
It sound efficient to me.   You could also automate that by having a small table that hold account IDs and a "run date."    The first time the script runs, it loads all account IDs into this table, then processes the first one, when finished, it enters the date into run date.  The next time it runs, it fetches the next account ID from the table and runs it, and updates the date.   It keeps pulling account IDs from the table until all dates are full.    When it runs again, it clears the table, loads the IDs and starts over.
0
 
Angelp1ayCommented:
Yep, agree, I would make your query dynamically select the next 5 unimported account IDs each time it's run.

To support this I would log the account IDs imported in some table as part of the run.
0
 
nmaranoAuthor Commented:
Experts-

Thanks for all of your input with this, I split the points between you.  A lot of good discussion above as well
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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