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 · Accepted Answer · 2013-12-19 06:32:05Z

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, 2013 at 6:32

answered Dec 19, 2013 at 6:24

Stefan Steiger

83k70 gold badges405 silver badges461 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Gautam Menariya Over a year ago

basically donation tabel have 30000+ and NorthGangotri have 20000+ entries. and i have to take almost all fields. any other options?

Stefan Steiger Over a year ago

@Gautam Menariya: Pagination or virtual rendering. Don't get & display all entries at the same time.

Gautam Menariya Over a year ago

i am using this dataset in crystal report. I also create view but it lso take more then 10 sec to display records.

Stefan Steiger Over a year ago

@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 ?

Gautam Menariya Over a year ago

and it is a desktop application

|

Collectives™ on Stack Overflow

create dataset from two tables

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related