Database

ZurdoDev7-May-15 4:08

ZurdoDev

7-May-15 4:08

Ankit RS wrote:
Any ideas/solutions would be much appreciated.

It sure does sound like you are hitting some sort of memory threshold. I'd suggest batching your records in smaller amounts.

There are only 10 types of people in the world, those who understand binary and those who don't.

Sascha Lefèvre7-May-15 6:25

Sascha Lefèvre

7-May-15 6:25

I second RyanDev's suggestion. I've seen a benchmark somewhere, comparing the insert speed depending on the batch size and it had diminishing returns starting around 50. I think it was for SQL Server but I'd imagine it's similar for other DBMS'. Which would mean you won't gain much anyway with a batch size of 1000.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Ankit RS8-May-15 0:36

Ankit RS

8-May-15 0:36

Thanks Ryan and Sascha.

I think I figured out the issue: it most certainly looks to be a threshold issue. On executing an insert inside a loop for 1 to 2000 in the query editor, Sybase throws up a pop-up stating "Maximum number of entries for the editor is 1000 and you should remove successful logs from the log file." Paraphrased. There are options to either remove or keep the successful entries from the log. See attached snapshot.

Surely, this is what's causing the insert to fail, when executed through Qlik, as there's no handler for the pop-up.

(Funny I can't locate the attach option here for the snapshot)

Edit: Turns out the solution is: 1) keep the number of rows in the insert less than 1000 for each execution (loop or otherwise), and 2) include the begin and commit the transaction statements as a part of the query itself instead of using the connection.BeginTrans and connection.CommitTrans in the VBScript code (which is what I was doing initially.)

modified 8-May-15 7:07am.

Sascha Lefèvre8-May-15 10:02

Sascha Lefèvre

8-May-15 10:02

Glad you solved it! Cheers Smile | :)

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Union on a join

jkirkerx6-May-15 9:20

jkirkerx

6-May-15 9:20

I got myself in a pickle here.
I started letting customers delete items in the store. But I copied the item into a separate table.
I should of added a column status instead.

But anyways, on my report for total items sold in the year, I did a join on the product table, to get the current cost, images, etc.
So now that some items have been deleted, the record is no longer in the product table, but in the recycle table.

I tried a union all, but it results in 2 records, in which the 2nd is null.
I tried just union as well, but it produces an reader error.
I'm not sure how to proceed on this, or what to words to use to do a search here.
I'm stumped!

[EDIT]
Maybe I should of done a if exist around the join, and just switch tables.

Dim query As String = _
"DECLARE @startDate AS DATE; " & _
"DECLARE @stopDate AS DATE; " & _
"SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0); " & _
"SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999); "

query +=
"SELECT " & _
" coH.PartNumber, " & _
" coH.ManPartNumber, " & _
" SUM(coH.Qty) as TotalQty, " & _
" CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost, " & _
" CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice, " & _
" pI.Cost, " & _
" pI.Price, " & _
" pI.ShortDescription, " & _
" pI.LongDescription, " & _
" pI.PostageImage, " & _
" pI.Thumbnail, " & _
" pI.VendorID, " & _
" pI.VendorName, " & _
" pI.Weight, " & _
" pI.ShipHeight, " & _
" pI.ShipWidth, " & _
" pI.ShipDepth, " & _
" pI.LimitedItem, " & _
" vI.Address1, " & _
" vI.Address2, " & _
" vI.City, " & _
" vI.StateCode, " & _
" vI.CountryCode, " & _
" vI.ZipCode, " & _
" vI.ContactName, " & _
" vI.VendorPhone " & _
" FROM CompletedOrdersCartHistory as coH " & _
" LEFT JOIN PRODUCTINFO AS pI " & _
" ON coH.PartNumber = pI.PartNumber " & _
" LEFT JOIN VendorInfo AS vI " & _
" ON pI.VendorID = vI.VendorID " & _
" WHERE coh.OrderDate > @startDate " & _
" AND coh.OrderDate < @stopDate " & _
" AND coh.PartNumber = @PartNumber " & _
" GROUP BY " & _
" coH.PartNumber, " & _
" coH.ManPartNumber,  " & _
" pI.Cost,  " & _
" pI.Price, " & _
" pI.ShortDescription,  " & _
" pI.LongDescription,  " & _
" pI.PostageImage,  " & _
" pI.Thumbnail,  " & _
" pI.VendorID,  " & _
" pI.VendorName, " & _
" pI.Weight,  " & _
" pI.ShipHeight,  " & _
" pI.ShipWidth,  " & _
" pI.ShipDepth,  " & _
" pI.LimitedItem, " & _
" vi.Address1,  " & _
" vI.Address2,  " & _
" vI.City,  " & _
" vI.StateCode,  " & _
" vI.CountryCode,  " & _
" vI.ZipCode,  " & _
" vI.ContactName,  " & _
" vI.VendorPhone "

query +=
" UNION ALL "

query +=
"SELECT " & _
" coH.PartNumber, " & _
" coH.ManPartNumber, " & _
" SUM(coH.Qty) as TotalQty, " & _
" CAST(SUM(coH.Qty * coh.Cost) AS Decimal(10,2)) as TotalCost, " & _
" CAST(SUM(coH.Qty * coh.Price) AS Decimal(10,2)) as TotalPrice, " & _
" pIH.Cost, " & _
" pIH.Price, " & _
" pIH.ShortDescription, " & _
" pIH.LongDescription, " & _
" pIH.PostageImage, " & _
" pIH.Thumbnail, " & _
" pIH.VendorID, " & _
" pIH.VendorName, " & _
" pIH.Weight, " & _
" pIH.ShipHeight, " & _
" pIH.ShipWidth, " & _
" pIH.ShipDepth, " & _
" pIH.LimitedItem, " & _
" vI.Address1, " & _
" vI.Address2, " & _
" vI.City, " & _
" vI.StateCode, " & _
" vI.CountryCode, " & _
" vI.ZipCode, " & _
" vI.ContactName, " & _
" vI.VendorPhone " & _
" FROM CompletedOrdersCartHistory as coH " & _
" LEFT JOIN PRODUCTINFO_RECYCLEBIN AS pIH " & _
" ON coH.PartNumber = pIH.PartNumber " & _
" LEFT JOIN VendorInfo AS vI " & _
" ON pIH.VendorID = vI.VendorID " & _
" WHERE coh.OrderDate > @startDate " & _
" AND coh.OrderDate < @stopDate " & _
" AND coh.PartNumber = @PartNumber " & _
" GROUP BY " & _
" coH.PartNumber,  " & _
" coH.ManPartNumber,  " & _
" pIH.Cost,  " & _
" pIH.Price, " & _
" pIH.ShortDescription,  " & _
" pIH.LongDescription,  " & _
" pIH.PostageImage,  " & _
" pIH.Thumbnail,  " & _
" pIH.VendorID,  " & _
" pIH.VendorName, " & _
" pIH.Weight,  " & _
" pIH.ShipHeight,  " & _
" pIH.ShipWidth,  " & _
" pIH.ShipDepth,  " & _
" pIH.LimitedItem, " & _
" vi.Address1,  " & _
" vI.Address2,  " & _
" vI.City,  " & _
" vI.StateCode,  " & _
" vI.CountryCode,  " & _
" vI.ZipCode,  " & _
" vI.ContactName,  " & _
" vI.VendorPhone "

query +=
"ORDER BY coH.PartNumber "

modified 6-May-15 15:32pm.

Re: Union on a join

Richard Deeming6-May-15 9:40

Richard Deeming

6-May-15 9:40

Since you're using a relatively recent version of MS SQL Server, something like this should work:

SQL

DECLARE @startDate AS DATE;
DECLARE @stopDate AS DATE;

SET @startDate = DATETIMEFROMPARTS(@Year, 1, 1, 0, 0, 0, 0);
SET @stopDate = DATETIMEFROMPARTS(@Year, @Month, @Day, 23, 59, 59, 999);

WITH cteProductInfo As
(
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO
    
    UNION ALL
    
    SELECT
        PartNumber,
        Cost,
        Price,
        ShortDescription,
        LongDescription,
        PostageImage,
        Thumbnail,
        VendorID,
        VendorName,
        Weight,
        ShipHeight,
        ShipWidth,
        ShipDepth,
        LimitedItem
    FROM
        PRODUCTINFO_RECYCLEBIN
),
cteOrders As
(
    SELECT
        PartNumber,
        ManPartNumber, 
        SUM(coH.Qty) as TotalQty,
        CAST(SUM(coH.Qty * coh.Cost) AS decimal(10, 2)) as TotalCost,
        CAST(SUM(coH.Qty * coh.Price) AS decimal(10, 2)) as TotalPrice
    FROM
        CompletedOrdersCartHistory
    GROUP BY
        PartNumber,
        ManPartNumber
)
SELECT
    coH.PartNumber,
    coH.ManPartNumber,
    coH.TotalQty,
    coH.TotalCost,
    coH.TotalPrice,
    pI.Cost,
    pI.Price,
    pI.ShortDescription,
    pI.LongDescription,
    pI.PostageImage,
    pI.Thumbnail,
    pI.VendorID,
    pI.VendorName,
    pI.Weight,
    pI.ShipHeight,
    pI.ShipWidth,
    pI.ShipDepth,
    pI.LimitedItem,
    vI.Address1,
    vI.Address2,
    vI.City,
    vI.StateCode,
    vI.CountryCode,
    vI.ZipCode,
    vI.ContactName,
    vI.VendorPhone
FROM 
    cteOrders as coH
    LEFT JOIN cteProductInfo AS pI
    ON coH.PartNumber = pI.PartNumber
    LEFT JOIN VendorInfo AS vI
    ON pI.VendorID = vI.VendorID
WHERE 
    coh.OrderDate > @startDate
AND 
    coh.OrderDate < @stopDate
AND 
    coh.PartNumber = @PartNumber
ORDER BY 
    coH.PartNumber
;

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Union on a join

jkirkerx6-May-15 11:40

jkirkerx

6-May-15 11:40

That looks interesting!

Ohhhhh, so that's how you union all the productinfo and productinfo_recyclebin together.
Yes I do understand the SQL now.

Wow, thanks for taking the time to look at it. I know it was a lot of code to look at
and could get confusing.

I'll give it a spin tomorrow.

Thanks Richard!

Re: Union on a join

jkirkerx7-May-15 12:24

jkirkerx

7-May-15 12:24

Worked like a charm!
Thanks!

Had to adjust a couple of things, but that got my mind into better understanding the SQL sample

postgresql VACUUM

V.5-May-15 23:55

5-May-15 23:55

So I managed to run into the error:

"ERROR: database is not accepting commands to avoid wraparound data loss in database "mydb" HINT: Stop the postmaster and use a standalone backend to vacuum that database. You might also need to commit or roll back old prepared transactions."

I followed google into the solution of restarting the database in standalone mode (like here[^]), but when I run:

SQL

VACUUM FULL mytable;

it gives me the message:

backend> VACUUM FULL mytable;
2015-05-06 09:45:09 UTC WARNING:  database "mydb" must be vacuumed within 999976 transactions
2015-05-06 09:45:09 UTC HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions.
2015-05-06 09:47:53 UTC WARNING:  database "mydb" must be vacuumed within 999975 transactions
2015-05-06 09:47:53 UTC HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions.
2015-05-06 09:47:53 UTC WARNING:  database "mydb" must be vacuumed within 999974 transactions
2015-05-06 09:47:53 UTC HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
        You might also need to commit or roll back old prepared transactions.
backend>

I have no idea what I can do with this.. Just continue the VACUUM for all tables?

thanks.

V.

(MQOTD rules and previous solutions)

Tim Carmichael6-May-15 4:52

6-May-15 4:52

It must be said... that really sucks.

Mycroft Holmes6-May-15 14:06

Mycroft Holmes

6-May-15 14:06

That should be marked as abuse!

Never underestimate the power of human stupidity
RAH

Tim Carmichael6-May-15 14:49

6-May-15 14:49

That is why I used the joke icon.

V.6-May-15 20:41

6-May-15 20:41

That's all right. I'm a big boy now.
Not that the joke is funny, my VACUUM command failed and I'm really worrying right now. This is the development database, but I'm really afraid for the production database following any minute.

I did a VACUUM on all tables for production except for one (the largest one) which fails to do anything Sigh | :sigh:

.

Anyway, continueing the search, with a positive attitude Smile | :)

V.

(MQOTD rules and previous solutions)

Mycroft Holmes6-May-15 22:04

Mycroft Holmes

6-May-15 22:04

I meant Tim's response...

I can't help as I have zero experience with MySql. You might check out if SQLServerCentral helps out with MySql or if there is a specialist site for the database. This sounds like a DBA level problem not a developer issue.

Never underestimate the power of human stupidity
RAH

V.6-May-15 22:06

6-May-15 22:06

Mycroft Holmes wrote:
I meant Tim's response...

I know ...

I am the DBA and developer and analyst and tester and ... Wink | ;)

V.

(MQOTD rules and previous solutions)

Mycroft Holmes6-May-15 22:16

Mycroft Holmes

6-May-15 22:16

V. wrote:
he DBA and developer and analyst and tester and ...

No you are the poor sod having to look after the database, you may have designed, built, populated the data, tuned the indexes and then broke the bloody thing. You are not a DBA, you just have to do that job.

Sorry I can't help!

Never underestimate the power of human stupidity
RAH

Tim Carmichael7-May-15 0:01

7-May-15 0:01

I've never used Postgres or I would help.
Can you do a backup and compress? Just grasping for help....

Tim Carmichael7-May-15 2:15

7-May-15 2:15

Again, no Postgres experience, but here is a possible link:

http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html[^]