create dataset from two tables

Question

i use following code to create this.

    Dim tableName(1), queryName(1) As String
    tableName(0) = "donation"
    queryName(0) = "SELECT top 10 * FROM donation"
    tableName(1) = "NorthGangotri"
    queryName(1) = "SELECT  * FROM NorthGangotri  " 



For I As Integer = 0 To UBound(tableName)
Dim Adapter As New SqlClient.SqlDataAdapter
Adapter = objclsDataUtility.GetDataAdeptor(queryString(I))
Adapter.Fill(DataSet, tableName(I))
Next

and it works fine. until I use top statement with query(TOP 5 /TOP 10).

but when i remove top statement, it takes very logn time to generate report. i think i did wrong something. Can anyone help me ?

Stefan Steiger · Answer 1 · 2013-12-19T06:32:05.250

1

Probably your table is very huge and your fetching all (*) fields. Maybe if you already have that many entries, you should limit your query to only the fields you need, instead of all fields. IO is a big database/network bottleneck.

Also, if you have any joins in your statement, maybe you have not set the foreign keys, which will make the query extremely slow if you have many entries in your tables.

Besides that, if you load a table on a website, it will get very slow in Internet Explorer if you have more than appx. 20 rows. Then, actually the data display and data transmission including viewstate may take very long. Switch off viewstate (if you use web forms), and use either paging or virtual rendering (AJAX, e.g. SlickGrid).

DECLARE @start integer 
DECLARE @end integer 

SET @start = 1 
SET @end = 20 

;WITH CTE AS
(
     SELECT 
             YOUR_TABLE.*
            ,ROW_NUMBER () OVER ORDER BY YOUR_TABLE.fieldXXX ASC) AS rn 
     FROM YOUR_TABLE
)

SELECT * FROM CTE 
WHERE rn BETWEEN @start AND @end

edited Dec 19 '13 at 06:32

answered Dec 19 '13 at 06:24

Stefan Steiger

78,642
66
377
442

basically donation tabel have 30000+ and NorthGangotri have 20000+ entries. and i have to take almost all fields. any other options? – Gautam Menariya Dec 19 '13 at 06:27
@Gautam Menariya: Pagination or virtual rendering. Don't get & display all entries at the same time. – Stefan Steiger Dec 19 '13 at 06:28
i am using this dataset in crystal report. I also create view but it lso take more then 10 sec to display records. – Gautam Menariya Dec 19 '13 at 06:33
@Gautam Menariya: Ah, that's an important piece of information. Did you test if your query is actually that slow, or whether it is Crystal Reports ? – Stefan Steiger Dec 19 '13 at 06:37
and it is a desktop application – Gautam Menariya Dec 19 '13 at 06:38
@Gautam Menariya: If I may give you some unrelated advice: If you have a Winforms application, use Microsoft ReportingService instead of CrystalReports. It's much faster. We used CrystalReports before, and it was nothing but problems, and scaled very poorly as well. See this link: http://stackoverflow.com/questions/168427/compare-sql-server-reporting-services-to-crystal-reports – Stefan Steiger Dec 19 '13 at 06:43
actully i am basically php developer. it is my first application in .net. Here i paste my code. http://sasasasasassdvfsvd.blogspot.in/ – Gautam Menariya Dec 19 '13 at 06:48
@Gautam Menariya: Probably really the data volume. Maybe you add a parameter from and to to your report, and use it for paging. – Stefan Steiger Dec 19 '13 at 06:53

create dataset from two tables

1 Answers1