INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Looping through a table to find records

Looping through a table to find records

(OP)
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.

RE: Looping through a table to find records

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!

RE: Looping through a table to find records

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.

RE: Looping through a table to find records

(OP)
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.

RE: Looping through a table to find records

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close