Solved

Limit List in Query

Posted on 2013-12-13
12
144 Views
Last Modified: 2014-01-25
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
Comment
Question by:nmarano
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 11

Expert Comment

by:N R
Comment Utility
Is the table indexed?
0
 
LVL 2

Author Comment

by:nmarano
Comment Utility
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
 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
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
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
 
LVL 2

Author Comment

by:nmarano
Comment Utility
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
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 39

Expert Comment

by:gdemaria
Comment Utility
> 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
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
> ...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
 
LVL 2

Accepted Solution

by:
nmarano earned 0 total points
Comment Utility
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
 
LVL 39

Assisted Solution

by:gdemaria
gdemaria earned 250 total points
Comment Utility
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
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 250 total points
Comment Utility
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
 
LVL 2

Author Closing Comment

by:nmarano
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now