How can I make my code faster!

Hi Everyone,

I have a SQL Database which is not relational database. It was designed and created long time ago and I need to export some data to CSV file.

Here is my code:

 

 

 

Imports System.Data.SqlClient

Imports System

Imports System.Data

Imports System.IO

 

Imports System.Data.OleDb

 

 

 

 

Public Class frmFirstExp

    Public dtResult As New DataTable

 

    Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click

 

        Dim i As Integer

        Dim j As Integer

        Dim SQL As String = " "

        Dim con As SqlConnection

        Dim ds As New DataSet

        Dim dsRec As New DataSet

        Dim AD As SqlDataAdapter

        Dim ADrec As SqlDataAdapter

        Dim dtRec As New DataTable

        Dim dtRecAdd As New DataTable

 

 

        SQL = "Select distinct ID from AddInItems "

        con = New SqlConnection("Data Source=TESTSHAREPOINT;Initial Catalog=Suncorp_062608;User ID=fShang;Password=12345; ")

        con.Open()

        AD = New SqlDataAdapter(SQL, con)

        AD.Fill(ds, "tblAllRec ")

 

        CreateDataTable()

 

        For i = 0 To ds.Tables(0).Rows.Count - 1

 

            ADrec = New SqlDataAdapter(SQLString(ds.Tables(0).Rows(i).Item(0).ToString), con)

            ADrec.Fill(dtRec)

 

            Dim workRow As DataRow

 

            workRow = dtResult.NewRow()

            workRow(0) = dtRec.Rows(0).Item(0).ToString

            workRow(1) = dtRec.Rows(0).Item(1).ToString

            workRow(2) = dtRec.Rows(0).Item(2).ToString

            workRow(3) = dtRec.Rows(0).Item(3).ToString

            workRow(4) = dtRec.Rows(0).Item(4).ToString

            workRow(5) = dtRec.Rows(0).Item(5).ToString

            workRow(6) = dtRec.Rows(0).Item(6).ToString

            workRow(7) = dtRec.Rows(0).Item(7).ToString

            workRow(8) = dtRec.Rows(0).Item(8).ToString

            workRow(9) = dtRec.Rows(0).Item(9).ToString

            workRow(10) = dtRec.Rows(0).Item(10).ToString

            workRow(11) = dtRec.Rows(0).Item(11).ToString

            workRow(12) = dtRec.Rows(0).Item(12).ToString

            workRow(13) = dtRec.Rows(0).Item(13).ToString

            workRow(14) = dtRec.Rows(0).Item(14).ToString

            workRow(15) = dtRec.Rows(0).Item(15).ToString

            workRow(16) = dtRec.Rows(0).Item(16).ToString

            workRow(17) = dtRec.Rows(0).Item(17).ToString

            workRow(18) = dtRec.Rows(0).Item(18).ToString

            workRow(19) = dtRec.Rows(0).Item(19).ToString

            workRow(20) = dtRec.Rows(0).Item(20).ToString

            workRow(21) = dtRec.Rows(0).Item(21).ToString

            workRow(22) = dtRec.Rows(0).Item(22).ToString

            workRow(23) = dtRec.Rows(0).Item(23).ToString

            workRow(24) = dtRec.Rows(0).Item(24).ToString

            workRow(25) = dtRec.Rows(0).Item(25).ToString

            workRow(26) = dtRec.Rows(0).Item(26).ToString

            workRow(27) = dtRec.Rows(0).Item(27).ToString

            workRow(28) = dtRec.Rows(0).Item(28).ToString

            workRow(29) = dtRec.Rows(0).Item(29).ToString

            workRow(30) = dtRec.Rows(0).Item(30).ToString

            workRow(31) = dtRec.Rows(0).Item(31).ToString

            workRow(32) = dtRec.Rows(0).Item(32).ToString

            workRow(33) = dtRec.Rows(0).Item(33).ToString

            workRow(34) = dtRec.Rows(0).Item(34).ToString

            workRow(35) = dtRec.Rows(0).Item(35).ToString

            workRow(36) = dtRec.Rows(0).Item(36).ToString

            workRow(37) = dtRec.Rows(0).Item(37).ToString

 

            dtResult.Rows.Add(workRow)

        Next

 

 

        Dim dt As DataTable = ds.Tables(0)

        Using Write As New System.IO.StreamWriter("C:\Documents and Settings\shosseinirad\My Documents\Suncorp\FirstExport.csv ")

 

            clsImpExp.ProduceCSV(dtResult, Write, True)’Exporting Data

           

        End Using

 

    End Sub

    Function SQLString(ByVal RefID As String) As String

        Dim Sql As String

        Sql = "select A.ID as ID, IsDeleted, CreationDate, LastModified, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Property Type' and t3.Label='Property Type' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Property_Type, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Property Type' and t3.Label='Subtype' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Subtype, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Address' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Address, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Development Name' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Development_Name, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Property Name' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Property_Name, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='City' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as City, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='State/Province' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as State_Province, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Location Description' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Location_Description, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Sale Info' and t2.Name='Location Address' and t3.Label='Township' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Township, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Property ID' and t2.Name='Public Records' and t3.Label='Legal Description' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Legal_Description, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Code' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Zoning_Code, "

 

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Jurisdiction' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Zoning_Jurisdiction, "

 

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Land Related' and t2.Name='Zoning Information' and t3.Label='Zoning Description' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Zoning_Description, "

 

        Sql &= "isnull((select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DecimalValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Sale Price' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),0) as Sale_Price, "

 

        Sql &= "convert(varchar(12),(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DateTimeValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Contract Date' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),9) as Contract_Date, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Grantor / Seller' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Grantor_Seller, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Grantee / Buyer' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Grantee_Buyer, "

 

        Sql &= "isnull((select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DecimalValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Assessed Value - Total ($)' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),0) as A_Value_Total, "

 

        Sql &= "isnull((select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DecimalValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Assessed Value - Land ($)' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),0) as A_Value_Land, "

 

        Sql &= "isnull((SELECT     t4.[Value] "

        Sql &= "FROM         dbo.Tabs t1 INNER JOIN "

        Sql &= "dbo.Sections t2 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "dbo.Fields t3 ON t3.FK_SectionID = t2.ID INNER JOIN "

        Sql &= "dbo.DecimalValues t4 ON t4.FK_FieldID = t3.ID "

        Sql &= "WHERE     (t4.FK_ReferenceID = A.ID) AND (t3.Label = 'Assessed Value - Improvements ($)') AND (t2.Name = 'General Information') AND  "

        Sql &= "(t1.Name = 'Transaction Data') "

        Sql &= "),0) as A_Value_Improve, "

 

        Sql &= "isnull((select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DecimalValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Cash Equivalent Sale Price ($)' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),0) as C_E_Sale_Price, "

 

        Sql &= "isnull((select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  DecimalValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Transaction Data' and t2.Name='General Information' and t3.Label='Total Tax Amount' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= "),0) as Tax_Amount, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Construction Type' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Construction_Type, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Number of Units' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Number_of_Units, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Property Condition' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Property_Condition, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Construction Description' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Construction_Description, "

 

        Sql &= "(select t4.Value  from  "

        Sql &= "tabs t1  join Sections t2 on (t2.FK_TabID=t1.ID)  "

        Sql &= "join Fields t3 on (t3.FK_SectionID=t2.ID)  "

        Sql &= "join  StringValues t4 on (t4.FK_FieldID=t3.ID) "

        Sql &= "where t1.name='Building' and t2.Name='Building Information' and t3.Label='Year Built' "

        Sql &= "and t4.FK_ReferenceID=A.ID "

        Sql &= ") as Year_Built, "

 

        Sql &= "(SELECT     t4.[Value] "

        Sql &= "FROM         dbo.Tabs t1 INNER JOIN "

        Sql &= "dbo.Sections t2 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "dbo.Fields t3 ON t3.FK_SectionID = t2.ID INNER JOIN "

        Sql &= "dbo.IntegerValues t4 ON t4.FK_FieldID = t3.ID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Gross Building Area (SF)') AND (t2.Name = 'Gross Building Area') AND (t1.Name = 'Building Size') "

        Sql &= ") as Gross_Building_Area_SF, "

 

        Sql &= "isnull((SELECT     t4.[Value] "

        Sql &= "FROM         dbo.Tabs t1 INNER JOIN "

        Sql &= "dbo.Sections t2 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "dbo.Fields t3 ON t3.FK_SectionID = t2.ID INNER JOIN "

        Sql &= "dbo.IntegerValues t4 ON t4.FK_FieldID = t3.ID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Usable Area (SF)') AND (t2.Name = 'Usable Area') AND (t1.Name = 'Building Size') "

        Sql &= "),0) as Usable_Area_SF, "

 

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN "

        Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "

        Sql &= "    dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t3.Label = 'Gross Land Area (Acres)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') and (A.ID=t4.FK_ReferenceID) "

        Sql &= ") as Grs_Land_Area_AC, "

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN "

        Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "

        Sql &= "    dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Gross Land Area (SF)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "

        Sql &= ") as Grs_Land_Area_SF, "

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN  "

        Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN  "

        Sql &= "    dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t4.FK_ReferenceID=A.ID) AND (t3.Label = 'Shape') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "

        Sql &= ") as Shape, "

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN "

        Sql &= "         dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "

        Sql &= "dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Frontage (Feet)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "

        Sql &= ") as Frontage_Feet, "

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN "

        Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "

        Sql &= "dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Depth (Feet)') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "

        Sql &= ") as Depth_Feet, "

 

        Sql &= "(SELECT DISTINCT t4.[Value] "

        Sql &= "FROM         dbo.Sections t2 INNER JOIN "

        Sql &= "dbo.Fields t3 ON t2.ID = t3.FK_SectionID INNER JOIN "

        Sql &= "dbo.Tabs t1 ON t2.FK_TabID = t1.ID INNER JOIN "

        Sql &= "             dbo.StringValues t4 ON t3.ID = t4.FK_FieldID "

        Sql &= "WHERE     (t4.FK_ReferenceID =A.ID) AND (t3.Label = 'Lot Size Dimensions') AND (t2.Name = 'Land Area Informaton') AND (t1.Name = 'Land Related') "

        Sql &= ") as Lot_Size_Dimensions "

 

 

        Sql &= "from AddinItems A  "

 

        Sql &= "where(A.ID=' " & RefID & "') "

        Sql &= "order by Property_Type "

        SQLString = Sql

    End Function

 

    Private Sub CreateDataTable()

        dtResult.Columns.Add(New DataColumn("ID"))

        dtResult.Columns.Add(New DataColumn("IsDeleted"))

        dtResult.Columns.Add(New DataColumn("CreationDate"))

        dtResult.Columns.Add(New DataColumn("LastModified"))

        dtResult.Columns.Add(New DataColumn("Property_Type"))

        dtResult.Columns.Add(New DataColumn("Subtype"))

        dtResult.Columns.Add(New DataColumn("Address"))

        dtResult.Columns.Add(New DataColumn("Development_Name"))

        dtResult.Columns.Add(New DataColumn("Property_Name"))

        dtResult.Columns.Add(New DataColumn("City"))

        dtResult.Columns.Add(New DataColumn("State_Province"))

        dtResult.Columns.Add(New DataColumn("Location_Description"))

        dtResult.Columns.Add(New DataColumn("Township"))

        dtResult.Columns.Add(New DataColumn("Legal_Description"))

        dtResult.Columns.Add(New DataColumn("Zoning_Code"))

        dtResult.Columns.Add(New DataColumn("Zoning_Jurisdiction"))

        dtResult.Columns.Add(New DataColumn("Zoning_Description"))

        dtResult.Columns.Add(New DataColumn("Sale_Price"))

        dtResult.Columns.Add(New DataColumn("Contract_Date"))

        dtResult.Columns.Add(New DataColumn("Grantor_Seller"))

        dtResult.Columns.Add(New DataColumn("Grantee_Buyer"))

        dtResult.Columns.Add(New DataColumn("A_Value_Total"))

        dtResult.Columns.Add(New DataColumn("A_Value_Land"))

        dtResult.Columns.Add(New DataColumn("A_Value_Improve"))

        dtResult.Columns.Add(New DataColumn("C_E_Sale_Price"))

        dtResult.Columns.Add(New DataColumn("Tax_Amount"))

        dtResult.Columns.Add(New DataColumn("Construction_Type"))

        dtResult.Columns.Add(New DataColumn("Number_of_Units"))

        dtResult.Columns.Add(New DataColumn("Property_Condition"))

        dtResult.Columns.Add(New DataColumn("Construction_Description"))

        dtResult.Columns.Add(New DataColumn("Year_Built"))

        dtResult.Columns.Add(New DataColumn("Gross_Building_Area_SF"))

        dtResult.Columns.Add(New DataColumn("Usable_Area_SF"))

        dtResult.Columns.Add(New DataColumn("Grs_Land_Area_AC"))

        dtResult.Columns.Add(New DataColumn("Grs_Land_Area_SF"))

        dtResult.Columns.Add(New DataColumn("Shape"))

        dtResult.Columns.Add(New DataColumn("Frontage_Feet"))

        dtResult.Columns.Add(New DataColumn("Depth_Feet"))

        dtResult.Columns.Add(New DataColumn("Lot_Size_Dimensions"))

 

    End Sub

 

 

 

End Class

 

 

I wander if there is a faster way to do this. This process takes about 6 minutes for 431 records but this is just a part of real database. There are 10000 records in real database.

 Is there any other way to do that?