OracleDataAdapter and Crystal Reports

[supakoopa]

Okay everyone, thank you in advance for any help whatsoever regarding this matter. I'm pretty good with vb.net so I'll help around whenever I can. I unfortunately do not know that much about Crystal Reports hence why I'm coming here.

 

I've been tasked with optimizing a program written in vb.net and C#. Basically, it's an actualarial science tool that calculates someone's benefits, bla bla, and then uses crystal reports to display the corresponding information.

 

So, with every new and exciting opportunity there are a lot of bumps in the road to optimization.

 

-----

 

Okay, so my problem is, this program basically creates a SQL statement based on parameters put in a form (ie date, specific plan _id , exc) and then calls it against a view in the Oracle Database.

 

To do this, the program first creates the correct SQL query based on the form parameters, here is a short excerpt of what I mean...

 

If strCallingMenu = "LatestTestCaseStatus" Then
               Path = "\\Migration\Reports" & "\LatestTestCaseStatus.rpt"
               DirectCast(Me.MdiParent, MenuRoute).AppStatusStrip.Items("StatusLabel").Text = "Latest Test Case Status Report"
               SQL = ""
               SQL = "select run_date_v, plan_id_v, testcase_id_v, csn_v, testcase_status_v from master where"
               SQL = SQL & " to_date(run_date_v,'MM-dd-yyyy') between to_date('" & Format(DateValue(dtFromDate.Text), "MM-dd-yyyy") & "','MM-dd-yyyy') and to_date('" & Format(DateValue(dtToDate.Text), "MM-dd-yyyy") & "','MM-dd-yyyy')"
               If cmbPlanID.Text <> "ALL" Then
                   SQL = SQL & " and plan_id_v='" & cmbPlanID.Text & "' "
               End If
               If cmbTestType.Text <> "ALL" Then
                   SQL = SQL & " and SUBSTR(TESTCASE_ID_V,INSTR(TESTCASE_ID_V,'_',1,2)+1,(INSTR(TESTCASE_ID_V,'_',1,3))-(INSTR(TESTCASE_ID_V,'_',1,2)+1))='" & cmbTestType.Text & "' "
               End If
               SQL = SQL & " order by plan_id_v, substr(testcase_id_v,1,2), to_number(substr(testcase_id_v,4,6)), SUBSTR(TESTCASE_ID_V,INSTR(TESTCASE_ID_V,'_',1,2)+1,(INSTR(TESTCASE_ID_V,'_',1,3))-(INSTR(TESTCASE_ID_V,'_',1,2)+1)), to_number(SUBSTR(testcase_id_v, INSTR(testcase_id_v,'_', -1,1)+1)) desc"
               ds_report = conn_app.conn_select(MainModule.vpas_oradb_conn_str, SQL, "CR_REPORT", Nothing)
           ElseIf strCallingMenu = "DetailedSummaryReport" Then
               Path = "\\Migration\Reports" & "\Detailed_Summary_Report.rpt"
               DirectCast(Me.MdiParent, MenuRoute).AppStatusStrip.Items("StatusLabel").Text = "Detailed Summary Report"
               SQL = ""
               'SQL = "select * from master where "
               SQL = "select * from reports where "
               SQL = SQL & " to_date(run_date_v,'MM-dd-yyyy') between to_date('" & Format(DateValue(dtFromDate.Text), "MM-dd-yyyy") & "','MM-dd-yyyy') and to_date('" & Format(DateValue(dtToDate.Text), "MM-dd-yyyy") & "','MM-dd-yyyy')"
               If cmbPlanID.Text <> "ALL" Then
                   SQL = SQL & " and plan_id_v='" & cmbPlanID.Text & "' "
               End If

 

Ok, so after that it connects to the Oracle DB and attempts to fill a dataset.

 

Try
           Dim DtSet As System.Data.DataSet
           DtSet = New System.Data.DataSet

           Dim vpas_conn As OracleConnection
           vpas_conn = createConnectionInstance(connstring, schema)

           Using vpas_conn
               Using cmd As New OracleCommand(abc, vpas_conn)
                   cmd.CommandType = CommandType.Text
                   Using adpt As New OracleDataAdapter(cmd)
                       Dim ds As New DataSet
                       adpt.Fill(ds, def)
                       Return ds
                   End Using
               End Using
           End Using
       Catch ex As Exception
           ExceptionHelper.LogException(ex)
           conn_select = Nothing
       End Try

 

 

Okay, so what ends up happening is that the program will sit on the

adpt.Fill(ds, def) line anywhere from 2-10 minutes based on how many records are getting returned. In my experience with databases, I have never known a query to take this long.

 

 

In my unique situation I'm not sure what to tackle first. The database view has over 2 million records and like 20-30 columns, a lot of null columns for records too. I'm using Oracle 10 g which will soon be 11g, and Visual Studio 2008 with Crystal reports.

 

 

I'm not sure how I can speed up the database process b/c it has indexes already, I'm not exactly positive on which columns have indexes though.

 

 

Any advice on how to focus closer in on the issue at hand... I don't expect a working solution but I'm not sure how much more I can do from a debug standpoint in Visual Studio 2008. Is there anyway I can see what's happening in that fill, if it's actually populating data or just waiting?

 

 

 

Thank you!

Edited June 23, 2011 by PlausiblyDamp

[PlausiblyDamp]

If you try executing the generated sql directly against the server does it take a comparable amount of time?

 

It might be worth checking the indexes on the tables to see if they are on the correct fields but you find that instr and substr can prevent the database from really taking advantage of indexes anyway on those columns.

[PlausiblyDamp]

If you try executing the generated sql directly against the server does it take a comparable amount of time?

 

It might be worth checking the indexes on the tables to see if they are on the correct fields but you find that instr and substr can prevent the database from really taking advantage of indexes anyway on those columns.

[supakoopa]

I think whoever made this view didn't include any indexes on the table. I'm making some progress, but I see what you mean about the substr problem.

 

I've been given access to another development region so we'll see. I have to copy that whole view over to the new location though.

 

I just hope improvements can be made considering these specific queries can sometimes take around 5-15mins to generate a 50-100 page crystal report.

 

I'll check back in soon with more info...