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

How to get results with CTE query rahter than Loop

I have created a stored procedure with loops that gives me the output I need, but it is slow. I have tried to use a CTE query, but do not know how to create the TotalSamples. The CTE creates the same number of rows as the looped query, but does not calculate TotalSamples. TotalSamples should be the distinct samplekey by site, collectionYear, germinationYear, plot, habitat
speciesBySiteTeatmentWithLoop.sql
speciesBySiteTreatment.sql
sampleSeedbank50.csv
createSeedbank.sql
siteTreatmentSpeciesSampleOutput.csv
siteTreatmentSpeciesOutputCTE.csv
0
dblankman
Asked:
dblankman
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Blankman,

A CTE won't necessarily replace a store procedure or the necessity of looping on data.  What the CTE does is allow you to break a complex query into smaller pieces and reuse some of the intermediate results without the database rebuilding those results.

If you can't visualize a query that will do what you want, neither a complex query nor a CTE will do what you want any better than your stored procedure.


Kent
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now