Simple, yet complicated, SQL Server data access question. 
Author Message
 Simple, yet complicated, SQL Server data access question.

I have a program that needs to pull data from an SQL Server Database.
I am currently running SQL Server 7, but will be moving to SQL 2000
before the project goes live.  I am pulling 5,765 columns from 192
tables.  I have one key (user_id) that I am using to pull the data.  We
do have a system in place so that I only need to pull tables that the
user is using, but other than that, I am currently just doing
simple "select f1,f2,f3...from t1 where user_id = 1" type queries.
Some of the table will have multiple records per user, and some won't,
but we know before performing the query which ones will and which ones
won't, if that helps any.  After I have the data, I perform a lot (a
really, really, lot) of manipulations and calculations on it, then I
put anything that has changed back into the database.

The question is this:
What is the absolute fastest method for pulling this data from the
database and for putting it back.  I am not interested in code size or
complexity.  I need the absolute fastest way to do it.  Our current
target is for the entire process to take .25 (yes, 1 quarter of a)
second.  The data manipulations are currently taking .1 second, but we
know of several ways to speed that up.

In framing your answers, I guess I actually would like to know two
things.  What is the absolute fastest method in VB, and what is the
absolute fastest method in another language that could update VB type
structures and still be faster than the VB method.


possible.  I have a max of one week to get this thing sped up.
(Currently running between 4 and 12 seconds.)

Sent via Deja.com http://www.*-*-*.com/
Before you buy.



Sun, 20 Apr 2003 03:21:43 GMT  
 Simple, yet complicated, SQL Server data access question.
Well, that's not really a VB question. It's really a database question,
so you need to use all the tricks that can be used to expedite database
retrieval. Some are: use criteria (WHERE or HAVING clauses) to extract
the minimum necessary number of rows; select only the minimum necessary
number of columns; make certain the columns used in criteria are
indexed.

Talk to your SQL Server DBA about "tuning" the database.

Ask in the SQL Server newsgroup (sorry, I don't know its name).

I'm not certain what determined your goal of .25 seconds; but if that
is just someone's idea of 'how fast it ought to be', as often is the
case, then it may be worthwhile to remind them that some things can be
done 'as fast as they can be done but no faster'.

I can remember some frustrated developers who burned themselves out
trying to attain an(some) unattainable goal(s) set by a manager who
knew it couldn't be attained, but thought that would inspire them to do
it as fast as it could possibly be done. What he didn't realize was
that it negatively affected their work on the remainder of the project.

Finally, I'm sending you an e-mail _copy_ of this response. In general,
the rule is "ask in the newsgroup, get your answer in the newsgroup".
It's not specific to this newsgroup, but you'll find more on that
subject in http://www.mvps.org/access/netiquette.htm. E-mail only
replies deny the answer to others who might make use of the
information, too. Newsgroups aren't just a place for unpaid consulting;
they are a place for teaching and learning, as well.


Quote:
> I have a program that needs to pull data from an SQL Server Database.
> I am currently running SQL Server 7, but will be moving to SQL 2000
> before the project goes live.  I am pulling 5,765 columns from 192
> tables.  I have one key (user_id) that I am using to pull the data.
We
> do have a system in place so that I only need to pull tables that the
> user is using, but other than that, I am currently just doing
> simple "select f1,f2,f3...from t1 where user_id = 1" type queries.
> Some of the table will have multiple records per user, and some won't,
> but we know before performing the query which ones will and which ones
> won't, if that helps any.  After I have the data, I perform a lot (a
> really, really, lot) of manipulations and calculations on it, then I
> put anything that has changed back into the database.

> The question is this:
> What is the absolute fastest method for pulling this data from the
> database and for putting it back.  I am not interested in code size or
> complexity.  I need the absolute fastest way to do it.  Our current
> target is for the entire process to take .25 (yes, 1 quarter of a)
> second.  The data manipulations are currently taking .1 second, but we
> know of several ways to speed that up.

> In framing your answers, I guess I actually would like to know two
> things.  What is the absolute fastest method in VB, and what is the
> absolute fastest method in another language that could update VB type
> structures and still be faster than the VB method.


as
> possible.  I have a max of one week to get this thing sped up.
> (Currently running between 4 and 12 seconds.)

> Sent via Deja.com http://www.deja.com/
> Before you buy.

--
 L. M. (Larry) Linson
  http://www.ntpcug.org - North Texas PC User Group - Visit and Join
  http://www.ntmsdevsigs.homestead.com - NTPCUG Developer SIGs
  http://homestead.deja.com/user.accdevel - Access examples

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 20 Apr 2003 06:22:47 GMT  
 Simple, yet complicated, SQL Server data access question.
I am chopping the amount of data as much as possible already.  We know
the .25 is attainable because the product we used last year did it in
that amount of time (granted, after 7 years of development).

My problem is that this program takes a bunch of pseudo-code typed in
by 30 CPAs in the back room (the proverbial room full of monkeys) and
converts that into a VB DLL that will process an entire united states
federal tax return.

The reason the .25 is such a requirement is that the tax site consists
of literally hundreds of pages of data entry that the user will have to
wade through to fill out their tax return.  Between every single page,
it has to completely recalculate the users return so that they can have
a running total of how much they owe or are due in the status bar.
And that is not just a simple return.  This program is (supposed to be)
used by the average user as well as the company's professional CPA's,
who may have clients with 5 or 6 business, etc.

As far as database keys, we only have one criteria we can limit on, and
it is the primary key in every table.

As far as "email only," I didn't intend the "only" part.  In just the
time it took me to write this, I have two new bugs to fix, have had
three of those CPA come in and ask how they should do something, and
have received 4 emails about other problems that might or might not be
happening.  On top of that, IRS e-file testing begins tomorrow (they
think) as well as my companies national convention (and most valued
selling spot) next wednesday.  Which means that at the very minimum,  I
have to get it running invisibly fast before then, and even earlier if
I expect to release it to testers prior to the convention.

In other words, I really don't have time to sit on Deja news all day
refreshing the screen and waiting for the number of messages in the
thread to change.  I need to get as many changes in as quickly as
possible to this code.  Any other developer working with no specs on
impossible deadlines will also tell you that they don't have time for
it either.  I won't be upset if a message is not sent directly to me,
but it does slow me down, and does mean that I may miss any messages
comeing in.  As long as I get a copy of the message, I don't care where
else other copies go.  Feel free to publish it in an online tutorial
about jobs not to sign on for or something.  I just need info, and,
unfortunately, need it quicker than it comes in.

Sent via Deja.com http://www.deja.com/
Before you buy.



Sun, 20 Apr 2003 06:43:48 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Complicated Record Handling on MS Sql server and MS Accesses Databases

2. Server has not yet been opened message with VB6, CRP7 and SQL-server

3. simple sql server / ado question

4. Simple Question - SQL Script and Access MDB Database Creation

5. simple vb/access sql question

6. Simple Access SQL question

7. sql server connect. Migration Access->sql server

8. SQL Server (Me yet again)

9. Very Simple Data Access Question

10. Help w/ Server Has Not Yet Been Opened using ASP, ADO, SQL

11. sql server connect. Migration Access->sql server

12. SQL 2000/Server not open yet error

 

 
Powered by phpBB® Forum Software