Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
IS NULL optimisation |
Fri, Dec 30 2016 5:49 AM | Permanent Link |
Matthew Jones | I have a SQL query with this condition:
WHERE ("erSentTime" IS NULL) I have an index on erSentTime. But this is not optimised, because it has to do a row scan. I can't find a way to tell ESB to make an index that will tell it the not-null entries. Is this possible? -- Matthew Jones |
Fri, Dec 30 2016 8:28 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
I've just tried here on a table. I added an index to a timestamp column and the index is used quite happily. Is the SQL you've shown the full query or is it a part of a sub query? Roy Lambert |
Fri, Dec 30 2016 8:48 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> I've just tried here on a table. I added an index to a timestamp column and the index is used quite happily. Is the SQL you've shown the full query or is it a part of a sub query? Full SQL: SELECT ALL "erID" AS "erID", "erEmailType" AS "erEmailType", "erEmailDestination" AS "erEmailDestination", "erEmailContent" AS "erEmailContent", "erRequestTime" AS "erRequestTime" FROM "EmailRequest" /* WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE */ WHERE ("erSentTime" IS NULL) ORDER BY "erRequestTime" The table: CREATE TABLE "EmailRequest" ( "RecordID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 32, INCREMENT BY 1), "erID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 32, INCREMENT BY 1), "erEmailType" VARCHAR(24) COLLATE "UNI", "erEmailDestination" VARCHAR(254) COLLATE "UNI", "erEmailContent" CLOB COLLATE "UNI", "erRequestTime" TIMESTAMP, "erSentTime" TIMESTAMP, "erSentResult" INTEGER, "erSentContent" CLOB COLLATE "UNI", "erReplicateIndex" BIGINT, "erHoldForAdmin" BOOLEAN DEFAULT FALSE, "erActionRef" INTEGER, CONSTRAINT "PrimaryKey" PRIMARY KEY ("RecordID") ) DESCRIPTION 'DatabaseFile' VERSION 0.02 READWRITE ENCRYPTED INDEX PAGE SIZE 2048 BLOB BLOCK SIZE 2048 PUBLISH BLOCK SIZE 1024 PUBLISH COMPRESSION 0 MAX ROW BUFFER SIZE 32768 MAX INDEX BUFFER SIZE 65536 MAX BLOB BUFFER SIZE 32768 MAX PUBLISH BUFFER SIZE 32768! Indices: CREATE INDEX "idxHoldForAdmin" ON "EmailRequest" ("erHoldForAdmin")! CREATE INDEX "idxRequestTime" ON "EmailRequest" ("erRequestTime")! CREATE INDEX "idxID" ON "EmailRequest" ("erID")! CREATE INDEX "idxActionRef" ON "EmailRequest" ("erActionRef")! CREATE INDEX "idxSendTime" ON "EmailRequest" ("erSentTime")! I just copied these from an existing table, and saw this issue. Then found it was there on the original too. -- Matthew Jones |
Fri, Dec 30 2016 8:50 AM | Permanent Link |
Matthew Jones | Hmm, just ran it again and didn't get the plan telling me it was a row scan.
Enable one of the full lines commented out instead to see the problem. -- Matthew Jones |
Sat, Dec 31 2016 2:29 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
I'm trying it here (using ANSI rather than UNI for my convenience) I enabled WHERE ("erSentTime" IS NULL) AND ("erRequestTime" > TIMESTAMP '2016-12-23 10:41:45.870') AND "erHoldForAdmin" <> TRUE I do get a partial row scan on the coule of rows of data I entered Row scan with one or more index scans eliminated due to cost (EmailRequest): 1 rows, 376B estimated cost which is fair enough - Tim's optimiser thinks that will be more efficient. I'm not going to spend all day entering data just to see if its a volume issue so can you post the full execution plan, and, just in case it is volume can you export the data and post that into the binaries? Export rather than dump the table because I can then suck it this end both as unicode and ansi Roy Lambert |
Sat, Dec 31 2016 4:56 AM | Permanent Link |
Matthew Jones | Hmm, okay, I think you have explained it! My table is either empty or only
has a few rows. The optimiser is being clever and skipping the index need. But my guard is looking for that text as a sign that I missed an index so I went looking. I presume that once it gets a real amount of data the index is worth it and the warning goes away. Thanks for your help in my learning. |
Sat, Dec 31 2016 5:22 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Matthew
>Hmm, okay, I think you have explained it! My table is either empty or only >has a few rows. The optimiser is being clever and skipping the index need. Ah - so you you were to lazy to enter wadges of data as well >But my guard is looking for that text as a sign that I missed an index so I >went looking. I presume that once it gets a real amount of data the index >is worth it and the warning goes away. Its a good idea to have that signal. What's a good idea is to check out each part separately and see what happens. Roy Lambert |
Mon, Jan 2 2017 6:09 AM | Permanent Link |
Matthew Jones | Roy Lambert wrote:
> Ah - so you you were to lazy to enter wadges of data as well 8-) Well actually, this was taken from a live application, and the tables emptied for re-use in another. Which is partly why I was surprised, given that I'd not noticed this warning in the running one, and it is shown once a second, so you'd think I would. It being empty/low volume is obvious for optimisation once it is pointed out. -- Matthew Jones |
This web page was last updated on Tuesday, September 17, 2024 at 04:19 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |