Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Identifying lack of indexes |
Fri, Jun 6 2014 11:14 AM | Permanent Link |
Matthew Jones | How can I identify missing indexes in ElevateDB?
In DBISAM, I use code to wrap a Query to request a plan, and then look to see if it is not optimal: > if Pos('UN-OPTIMIZED', xQuery.Plan.Text) > 0 then > LogReport('Query not optimal ' + xQuery.Plan.Text); This works really well because if I do a "WHERE Column < 500" and the table has no index, it logs it, and I can find and fix this. In ElevateDB the Plan doesn't mention such things. I modified a table to remove an index to see what the plan report would change do without it, but nothing much changes - it doesn't mention how inefficient it is or that there is a "all items scan" needed etc. Is there a way I can achieve what I am wanting? I've always found this to be a good way to spot cases where I've modified the SQL and not got an index, causing inefficiency. Thanks, -- Matthew Jones |
Fri, Jun 6 2014 11:37 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
It actually gives more help than DBISAM but its different. Post a plan or two and I'll see if I can point out the relevant bits. Roy Lambert |
Fri, Jun 6 2014 12:35 PM | Permanent Link |
Matthew Jones | Okay, Plan posted below. I removed a load of fields that don't matter,
and hacked some names to protect the innocent fields. The key is that I dropped the index on "MyOrderState" which should really be indexed IMO, but which doesn't get one. -- Matthew Jones ======================================================================== ======== SQL Query (Executed by ElevateDB 2.16 Build 2) Note: The SQL shown here is generated by ElevateDB and may not be exactly the same as the SQL that was originally entered. However, none of the differences alter the execution results in any way. ======================================================================== ======== SELECT ALL "MyCreateTime" AS "MyCreateTime", "MyOrderGUID" AS "MyOrderGUID", "MyAddressShipJSON" AS "MyAddressShipJSON", "MyOrderRef" AS "MyOrderRef", "MyOrderState" AS "MyOrderState", FROM "MyOrder" INNER JOIN "MyAccount" ON ("MyCustomerRef" = "ACustomerRef") WHERE ("MyOrderState" >= 200) AND ("MyAdminComplete" IS NULL) ORDER BY "MyOrderNeedsShipping" DESC, "MyCreateTime" Source Tables ------------- CustomerOrder: 73 rows CustomerAccount: 10 rows Result Set ---------- The result set was insensitive and read-only The result set consisted of zero or more rows Filtering --------- The following filter condition was applied to the CustomerOrder table: ("MyAdminComplete" IS NULL) Index scan (CustomerOrder.idxAdminComplete): 21 keys, 2KB estimated cost AND ("MyOrderState" >= 200) Row scan (CustomerOrder): 21 rows, 8.7KB estimated cost Joins ----- The driver table was the CustomerOrder table The CustomerOrder table was joined to the CustomerAccount table with the inner join expression: ("MyCustomerRef" = "YourCustomerRef") The optimizer attempted to re-order the joins to a more optimal order The joins were already in the most optimal order The following join condition was applied to the CustomerAccount table: ("caCustomerRef" = "MyCustomerRef") Index scan (CustomerAccount.idxCustomerRef) Result set I/O statistics ------------------------- Total rows visited: 2 Row buffer manager Max buffer size: 1023.75KB Buffer size: 2.84KB Hits: 2 Misses: 0 Hit ratio: 1 Reads: 0 read: 0B Writes: 0 written: 0B Index Page buffer manager Max buffer size: 2MB Buffer size: 8KB Hits: 2 Misses: 0 Hit ratio: 1 Reads: 0 read: 0B Writes: 0 written: 0B BLOB Block buffer manager Max buffer size: 2MB Buffer size: 8KB Hits: 8 Misses: 0 Hit ratio: 1 Reads: 0 read: 0B Writes: 0 written: 0B ======================================================================== ======== 2 row(s) returned in 0 secs ======================================================================== ======== |
Fri, Jun 6 2014 12:48 PM | Permanent Link |
Uli Becker | Matthew,
you should search for "Row scan" That indicates that all rows are scanned, in many cases because of a missing index. Uli |
Sat, Jun 7 2014 3:53 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
As Uli says look for Row Scan, but equally important is look for the estimated cost. Also, I'm not sure if I'm right here but the sizes of the table are important Source Tables ------------- CustomerOrder: 73 rows CustomerAccount: 10 rows Nothing much you do with tables of this size is going to make much difference. Try with a few thousand rows and you may get a difference (at least that's how I'd do it if I were Tim) What follows is my guessing, and how I interpret it. Reality may well disagree >Filtering >--------- >The following filter condition was applied to the CustomerOrder table: >("MyAdminComplete" IS NULL) >Index scan (CustomerOrder.idxAdminComplete): 21 keys, 2KB estimated cost >AND >("MyOrderState" >= 200) >Row scan (CustomerOrder): 21 rows, 8.7KB estimated cost What this tells us is that first of all the tables were filtered by MyAdminComplete IS NULL that selection was carried out using the index CustomerOrder.idxAdminComplete and that it resulted in 21 rows for further checking and only 2KB of (index) data had to be examined to make the selection. Next the 21 rows that came from the first filter were examined on a row by row basis to see if they should be included. SO 21 rows were checked and that involved comparing 8.7KB of data. Now we move on to the realm of metaphysics If an index existed for MyOrderState I would guess that it wouldn't be used anyway. The costs of checking that index, creating a list of rows to include in the result set, checking the idxAdminComplete index and creating a list of rows for inclusion and then combining the two lists excluding anything that wasn't in both to produce a list giving the rows for the final result set would be higher than simply doing a row scan. If you have lots more data in the tables and the "MyAdminComplete" IS NULL test produced several thousand candidates for inclusion in the result set things may well change. I am eagerly awaiting the day Tim has the time to write an article on interpreting these beasties Roy Lambert |
Sat, Jun 7 2014 4:25 AM | Permanent Link |
Matthew Jones | Roy Lambert <roy@lybster.me.uk> wrote:
> As Uli says look for Row Scan, but equally important is look for the > estimated cost. Also, I'm not sure if I'm right here but the sizes of the > table are important > [snip] > Nothing much you do with tables of this size is going to make much > difference. Try with a few thousand rows and you may get a difference (at > least that's how I'd do it if I were Tim) Indeed, this is very much a test database and I expect it to get a lot larger over time. The Row scan is what I will look for in my test, and will work out indexes from that. Most times one index fixes loads of queries. As for it working out costs, I'm happy for it to ditch the index if it thinks that helps. The main thing is that I want it to have the option. I will be getting others to test my shop more soon... Thank you both. -- Matthew Jones |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |