Looping through a table to find records

Question

Looping through a table to find records

thorny00 (Programmer)

(OP)

3 Nov 16 18:41

Thanks in advance for all of your time and help. It is greatly appreciated!!

First, I need to find the record where -
METRIC_INTERNAL_NAME = 'LOGIN' AND DATA_MNEMONIC_ID = 'DEP'
Then collect all records until
METRIC_INTERNAL_NAME = 'LOGOUT' AND DATA_MNEMONIC_ID = 'TIMEOUT'
then loop through and find the next records where the conditions above are true.

I was thinking (but what do I know) of writing each group of records to a temp table.

djj55 (Programmer) · Answer 1 · 2016-11-03 19:12:02

I assume your table has a indexed time column as SQL Server does not always store records in the order they are created.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

Olaf Doschke (Programmer) · Answer 2 · 2016-11-03 19:21:03

djj55 addresses the most important counter question: In which order? Chronological order seems plausible considering this has to do with LOGIN and LOGOUT events.

Typically any sql query is an iteration of data, it just needs an ORDER BY sometime and where clauses for lower and upper bound (WHERE sometime>X AND sometime<Y) to get the portion of data you want.

Bye, Olaf.

thorny00 (Programmer) · Answer 3 · 2016-11-03 19:26:40

Here is the worst part- the data is coming from a view which is gigantic to say the least. So, the view does NOT have an index on it.

Olaf Doschke (Programmer) · Answer 4 · 2016-11-03 19:29:57

Not at all shocking, because a view is a query on a table. So what's the query? You can find out, modify it and do the modified query - or create a new view - or simply select from the view in as if it was a table.

FYI: You jump on false conclusions, if you think you can't make use of table indexes when querying a view, just because thew view itself is not materialized and has no index.

1. I have two tables headtable and detailtable and did the following query, which is optimized by indexes:

CODE

select * from headtable left outer join detailtable on headtable.id = detailtable.parentid
 where sometime<X order by sometime

2. I created a view (view_all) with this query:

CODE

select * from headtable left outer join detailtable on headtable.id = detailtable.parentid

3. I used this query on the view and it is optimized on the same indexes as the original query is:

CODE

Select * from view_all where sometime<X order by sometime

So indeed querying a view you make use of all indexes the underlying tables have, as a view is nothing but a query and queries are optimized, if you use a view as a table it's just like using a CTE, and CTEs also make use of indexes. Views themselves have no indexes, because they have no permanent data (unless you create a 'materialized view, but that's not applicable for such log data). You have all the advantages (and disadvantages) of the optimization of queries with a view or a query including a view. You can imagine the view query used as aliased (named) subquery and the overall query is analyzed and optimized.

Bye, Olaf.

Come Join Us!

Posting Guidelines

Jobs

Looping through a table to find records