Sql Database Connection Extremely Slow - Bartender 2016
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).
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
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
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
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.
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
i think i got the same problem.
using bartender 10.1 sr40
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?-1
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
I don't know myself. Again I suggest you contact technical support.0
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
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/0
I have same problem.
We use Live database from a website server and we need to make Inner Join 3 tables of SQL 2019 to find name and barcodes of Pieces for each product Kit to make a Packing List Label.
We have only 2000 products up to now, It's too slow when open a template and click on print or click on select record button on print dialog box. some time it take 5 minutes for each label print! Actually we have only 5-10 seconds time to print each label.
Is there any option to read database for one time and save in BarTender Cash and never use live database again to save time? for refresh we will click on reload for example to read database again. for each action as open file, print, print preview, select records, etc. we should wait for minutes to database loading data.... extremely slow.
Is there any fast way to reading database one time only and use several time?0
Armin: As per the previous comments, did you try creating a View of your table join on the SQL Server itself, and then have BarTender connect to this View in a direct fashion. Generally speaking, taking this approach will provide better database performance for any application that is attempting to query the database to select records.
In newer versions of BarTender, and with the Automation edition or above, you could build a Database integration, that monitors the View for new inserted records, and once detected, use that record data source to insert a new record into a different database that is local to BarTender, and thus have your BarTender document connect to that locally cached database for printing purposes.0