SQL statement syntax order of statements
Help me understand the way enVision compiles reports.
Is there a way to optimze the syntax in a SQL statement for reports? Here is an example of what I mean:
(1) MessageID = 'event' AND (username = 'bob' OR eventuser = 'bob')
(2) (username = 'bob' OR eventuser = 'bob') AND MessageID = 'event'
Does enVision first go through the IPDB when it compiles the report and look for the first condition, i.e., MessageID...effectively grabbing 'event' that equals the critera and then uses the other conditions of username or eventuser?
If that is the case...then the second query would be quicker - go through the IPDB and grab the username/eventuser of 'bob' and then narrow down the messageID event I am looking for.
I hope this makes sense...but effectively I'm trying to determine if the way I write my SQL statements makes a difference in how fast and efficiently I can generate reports. Any thoughts? If this is the case, could you provide some general rules to follow? Thanks!
you're correct about the 2nd one, envision 1st of all filters out all the events based on the indexed fields i.e. messageid, type etc..
than does the internal filters for the regular fields that's why if you choose messageid as the 1st condition in a report you will see 2 thing:
1 - the report runs a looooot faster
2 - envision cuts the messageid out of the data so for example if you have 200000 general events and only 25 of them are with the specific messageid - when you'll run the report you'll see when it's populating instead of going over 200000 events for example you will see it cuts out only 25 events that correspond to the selected messageid.
hopes it helps.