If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Pervasive.SQL > Need help with a slow query pls :-((

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Need help with a slow query pls :-((

UPDATE "Schema" SET varLid = 'S'

FROM
(SELECT pNumber, productName, varLid, MAX(dato) FROM "Schema"
WITH (index (SepOldTempIndex))
WHERE varLid = 'T'
GROUP BY pNumber, productName, varLid HAVING MAX(dato) <= '20010101') AS myData

WHERE myData.varLid = 'T'


The DB "Schema" can have more than 1,000,000 records,
We are using pervasive SQL.
All fields (varLid, pNumber, productName etc are strings)

Running the query "as is" means that it updates all records (not just within the select) in under 6 minutes.. (its only going to be run once). Adding more conditions ie: AND Schema.pNumber = myData.pNumber makes it run extrememly slow.

I need it to update only within the select. I can't using an alias on the UPDATE (pervasive won't allow it). Finaly.. just to make this even more interesting... the DB is VERY old and does NOT have any unique identifiers.

I would be MOST grateful with any suggestions.

Thankyou in advance.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Dec 2001
Posts: 1,069
What exact version of Pervasive are you using?
What indexes are defined on the table? Indexes don't have to be unique for good performance.
Your query doesn't make a lot of sense. You're selecting several fields but only updating one. Can you give an example of the existing data and what you want it to be?
__________________
Mirtheil Software
Certified Pervasive Developer
Certified Pervasive Technician
Custom Btrieve/VB development
http://www.mirtheil.com
I do not answer questions by email. Please post on the forum.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Pervasive 11 is the version.

INDEX:
CREATE INDEX "SepOldTempIndex" ON "SCHEMA"("dato" DESC, "pNumber", "productName")

The scenario is as follows:

VarLid is to be set as 'T' when the dato of the product does not exceed the selected dato.. Some products are refreshed past this date ( a continuation of sorts). If the productName has a date that continues past the selected date then it should not be included in the update.

The pNumber is a customer id of sorts.. this is not unique in that it can be repeated (still same customer... just maybe a different product) I do not have a unique ID to work with :-(.

As I said the DB is old.. and possibilities are limited... the problem comes (as far as I can see) is that the select works fine.. but it updates the entire database...

When I tried to use an alias on the update.. I was not allowed to... so if I check, for an example : WHERE myData.pNumber = Schema.Pnumber etc it is EXTREMELY slow.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 291
Since “schema” is a meta data term, it should never be a table name. You also have not learned the ISO-11179 rules for data element names, as you said, it is a legacy database (aka “family curse” ?).

The UPDATE.. FROM.. syntax is a bad idea. Its history goes back to 1970's Sybase and pre-ANSI days. Its semantics have changed from vendor to vendor; it used to do multiple updates on the same rows.
It is a bitch to optimize and you cannot safely port your code.

>> var_lid is to be set as 'T' when the dato of the product does not exceed the selected dato. Some products are refreshed past this date (a continuation of sorts). If the product_name has a date that continues past the selected date then it should not be included in the update. <<

I would guess you will be able to use [NOT] EXISTS() and get better performance.

UPDATE "Schema"
SET varl_id = 'S'
WHERE varl_id = 'T'
AND NOT EXISTS
(SELECT *
FROM "Schema" AS S1
WHERE S1.varl_id = 'T' -- redundant
AND S1.dato > '2001-01-01' -- past date
AND ..); -- more conditions


>> The DB "Schema" can have more than 1,000,000 records [sic], We are using pervasive SQL. All fields [sic] (var_lid, p_nbr, product_name etc are strings). The DB is VERY old and does NOT have any unique identifiers. <<

Rows are not records and columns are not fields. This was designed as a paper file system and there is no way to have any DRI. You probably got that far.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 3
Thx for the responses... ended up coding my way out of it in c# instead... I did learn to realise my utter HATE of badly designed databases from the late 90's though..

Thx again for the response.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On