Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server Syntax not fast enough for Crystal Reports .NET

Posted on 2010-08-19
8
Medium Priority
?
259 Views
Last Modified: 2012-06-21

Good Day:

I am using SQL Server 2000 for a VB.NET application.

The following script works great when I manually execute the first Delete Script, wait a minute and then execute the second Insert Script.  If I do not wait then it reports my old data instead of the new data.  In other words, SQL Server does not process the data fast enough for the Crystal Reports document.  Is there a way to fix this because I need to automate the process by using a stored procedure?  

---Delete Script
BEGIN TRAN
DELETE FROM PackSlip
WHERE SONumber=@SONumber
COMMIT TRAN

---Insert Script
BEGIN TRAN
INSERT INTO PackSlip
Select * from vwPackSlipCreate
WHERE SONumber=@SONumber
COMMIT TRAN

Sample Data:
PackSlip Table  (Old Data)
Order #:      1234
QtyOrdered:      10
QtyShipped:        5
QtyBO:        5      

PackSlip Table  (New Data)
Order #:      1234
QtyOrdered:      5
QtyShipped:      5
QtyBO:      0        

Any suggestions?
Thanks,
Denise

0
Comment
Question by:DeniseGoodheart
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 1000 total points
ID: 33480798
For the delete, make sure there is a good index on PackSlip.SONumber.
Other factors that may affect it are triggers, index updates and indexed views.

For the insert, it looks like you are using a view; maybe the view is not optimized?
Pick an SONumber, and run this in SSMS and check the execution plan (Ctrl-L).

Select * from vwPackSlipCreate WHERE SONumber= 123

If you need help, you can do this and we can check the view performance.
Open a new Query Window, run the tsql below

set showplan_text on;
Select * from vwPackSlipCreate WHERE SONumber= 123

Close the new query window.
0
 
LVL 59

Assisted Solution

by:HainKurt
HainKurt earned 1000 total points
ID: 33480823
after deleting make sure you re select / re run your select query...
there is no way to get the old data unless sql has some select bugs ;)
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 33482248
Does the report have data saved?

How are you running the report?

mlmcc
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 11

Expert Comment

by:mattibutt
ID: 33482862
2000 is 10 years old vb itsel is a slow language
0
 

Author Comment

by:DeniseGoodheart
ID: 33604095
mattibutt:

Yes I undestand 2000 is 10 years old but it is easier to use than 2005 and 2008 because the GUI changed.  I will upgrade to SQL 2008 when we are ready to go into production.  VB.NET is a slow  language compared to what other lanuages?
0
 

Author Comment

by:DeniseGoodheart
ID: 33604101
mlmcc:

I'm using Crystal Reports.NET
0
 

Author Comment

by:DeniseGoodheart
ID: 33604116
Hi All,

I did a quick fix to fix this  problem, but now I am getting the same problem in another stored procedure.  It might  be that I need to extend my connection time on VB.NET.  I will try this and if it does not work, then I will post a new thread.

Thanks
0
 

Author Closing Comment

by:DeniseGoodheart
ID: 33604122
It did not fix the problem, but they did give me valuable information.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question