Sql Database Connection Extremely Slow - Bartender 2016 关注

1
Avatar
Legacy Poster

Anything I do on the template takes form 3 to 5 minutes. Opening the file, setting up datasource for a barcode, preview and others things take 3 to 5 minutes... seems like the software access it the database each time... If I had already specified the data connection why does it try to connect everytime? (that is what i believe is doing).

11 评论

0
Avatar
Legacy Poster
评论操作 固定链接

I am using a query like this: 

 

SELECT T3.SALESID,T1.PACKINGSLIPID,T3.CUSTOMERREF ASN,'2S' + T3.CUSTOMERREF PREASN,T3.PURCHORDERFORMNUM PO,'K' + T3.PURCHORDERFORMNUM PREFPO,T2.ITEMID,'P' + T2.ITEMID PREITEMID,T4.CUSTOMERLINENUM,'4K' + LTRIM(STR(T4.CUSTOMERLINENUM)) PRELINE,(T2.QTY * -1) QTY,T4.SALESUNIT,'3Q' + T4.SALESUNIT PREUNIT,'Q' + LTRIM(STR(T2.QTY * -1)) PREQTY FROM SALESSHIPPINGSTAT T1 INNER JOIN INVENTTRANS T2 ON T1.DATAAREAID = T2.DATAAREAID AND T1.PACKINGSLIPID = T2.PACKINGSLIPID INNER JOIN SALESTABLE T3 ON T1.DATAAREAID = T3.DATAAREAID AND T1.ORIGSALESID = T3.SALESID INNER JOIN SALESLINE T4 ON T1.DATAAREAID = T4.DATAAREAID AND T3.SALESID = T4.SALESID AND T2.INVENTTRANSID = T4.INVENTTRANSID WHERE T1.DATAAREAID = '520 ' AND T1.PACKINGSLIPID = '?PackingSlip'

0
Avatar
Legacy Poster
评论操作 固定链接

Granted there are more than 500,000 records when joining those tables, but that is why I am using the Prompt on Database Setup to narrow to very few records.

0
Avatar
Ian Cummings
版主
评论操作 固定链接

If you have live database data enabled in the template design then BarTender will connect to the database upon opening the label.  Otherwise this would be limited to preview, printing or database set-up.

 

Check to see if a server or client based database cursor gives better performance.

 

Perhaps if you create a view in SQL server, and have BarTender connect to and query that you'll get better performance...

0
Avatar
Legacy Poster
评论操作 固定链接

Could you share with me screen on where can I find out:

1- Live Database Data Enabled?

2- Based Database Cursor? 

 

I am using BarTender 2016.

 

NOTE: I already create the view to perform the joins on the server side.

 

Regards, 

0
Avatar
Legacy Poster
评论操作 固定链接

Please help... I hate to have to use Excel to update a table and then have Bartender connect to the excel file... I want to be able to do it directly from the SQL View I have created.

0
Avatar
Legacy Poster
评论操作 固定链接

i think i got the same problem.

 

using bartender 10.1 sr4

-1
Avatar
Ian Cummings
版主
评论操作 固定链接

hzm_mx: We're still looking into this issue.  In fact would it be possible for you to contact our technical support team for your region (http://www.seagullscientific.com/support/technical-support/) as we've had a few reports along these lines and would like to get to the bottom of it.  However, to do that we'd need to setup a reproducible case on a debug version of the software.  Is that something you could help us with?

0
Avatar
Noffie
评论操作 固定链接

I can confirm that I was experiencing this, and then switching my multi-join query to be a View, and referencing the View with my BartTender SQL Statement seems to have resolved the issue.  Ian - has there been any progress on understanding why this happens?  I fired up ProcMon even while Bartender kept freezing up on me, and realized that it looked like TCP traffic between my host and the database server was consistent with the time that the Bartender "lock-ups" occurred (and I filtered procmon to only show activity associated with Bartend process).

0
Avatar
Ian Cummings
版主
评论操作 固定链接

I don't know myself.  Again I suggest you contact technical support.

0
Avatar
Paul Svensson
评论操作 固定链接

When creating a new template, I always modify the source view to select TOP 1, so as to reduce the time it takes to return the result set. After, the template is setup, I simply remove the TOP 1 and the template will operate with the full result set. Additionally, we have a test and prod environment, if the view and field and database names are identical, you can simply change the server name and that does not impact the template mappings. The test environment could be setup to return fewer rows for the purpose of developing and testing the templates, then simply modify the server to the production server and save, and you are good to go.

0
Avatar
Ian Cummings
版主
评论操作 固定链接

As a general rule, one would always expect to get better performance connecting to a view, particularly if multiple tables are used, as this allows the SQL Server DB engine to optimize some processing ahead of time.

Make sure you update BarTender 2016 to the latest service release, currently R7, as I believe we've done some work fixing some DB related issues.  Get the service release from here: https://www.seagullscientific.com/support/service-releases-legacy-versions/2016/ 

请先登录再写评论。