Select a subset of data on three large databases

Question

I am trying to read from 3 separate databases using dynamic user defined criteria (Every query will be different). Then to display the result of that query (100,000+ rows) on a website. Everything currently works, but the queries can take over a minute.

So far my best solution is to use Data Virtualization or Paging, where I could use just an Ajax Call to Query Server for the next page, or next 100 rows. I am pretty new to SQL, so I don't know how to make that query.

On the server side:

    SELECT <ATTRIBUTES> FROM DB1, DB2, DB3 WHERE ROW_NUM > lowerLimit 
    AND ROW_NUM < upperLimit

However this doesn't work because as far as I can tell there isn't a ROW_NUM variable in SQL server.

On the client I just want to do something along the lines of this where I would just get the next 100 rows.

    $("#nextPage").click(function () {
        //get the string from the textbox
        start += 100;
        end += 100;
        loadFromDataBase(start, end);
    });

I thank you in advance, and please let me know if you have any better ideas. I am pretty new :)

Apply a [limit/offset in SQL Server](http://stackoverflow.com/questions/9013177/mysql-limit-clause-equivalent-for-sql-server#answer-9013516) — Nate Whittaker, Jul 07 '14 at 18:20

score 0 · Answer 1 · answered Jul 07 '14 at 18:11

0

We were able to use paging by doing:

SELECT TOP N <ATTRIBUTES> FROM DB1, DB2, DB3 ORDER BY Field

Where N is the page size the end user chooses and Field can also be chosen, but has a default. Does that make sense?

answered Jul 07 '14 at 18:11

Dave.Gugg

6,561
3
24
43

That makes sense, but I guess I am a little confused about how you would get a particular range of values. For example if N = 100, it would return the first 100 results. But then how would I get the next 100 results ? Sorry I'm new I might just be missing something obvious. Thanks – user3386190 Jul 07 '14 at 18:19
Take a look at the link Nate Whittaker posted under your question - that is a more robust explanation. – Dave.Gugg Jul 07 '14 at 18:22

Select a subset of data on three large databases

1 Answers1