I'm not sure if want I'm thinking makes sense. I have a SQL Server database table with 30 million rows.
If I do a select A, B, C, D from huge_table --- (about 30 million--- total 4 GB footprint) --- does it take additional time for the SQL Studio Client to actual display all 30 million of these than it would an application to fetch (and process) the exact same query? (with no display requirements).
I mean -- just to fetch two columns --- a datetime and an int -- from this 30 million record table that has 8 columns total --- well it takes 6 minutes to pull up all the records in SQL Studio. That's with no additional joins, calculations, or even every field.
Granted, it probably depends on my local client hardware (decent enough) -- and also the database is in Europe, and I'm in the United States -- though in practice I've found that maybe makes a query 1.5x slower at most.
I suppose I'm used to dealing with smaller datasets (like 2 million).
Is this simply an inherent lag in SQL studio, or does it suggest a reasonable fetch time for a similar hardware spec application?
I'm wondering how to 'speed up' this query, which by design, is a full scan. I can chunk it into smaller pieces, but the total time would be the same.
Should I aggregate or pre-calculate the 30 million? To be honest, that will not save time since with all the necessary dimensions, the 30 million would condense to maybe 25 million plus aggregate processing time - it would take longer.
Indices wouldn't help with full table fetches. Right? You're saying return these fields from the full table.
What's a reasonable time to fetch 30 million records? Select A,B,C from big_table?