Re: limits of SQL Server???
From: Andrew J. Kelly (sqlmvpnooospam_at_shadhawk.com)
Date: 02/15/04
- Next message: Mark Lang: "SQL Server 2000 Eval won't install"
- Previous message: bstevenson_at_agilera.com: "Re: Problems setting sqlagent proxy account"
- In reply to: mickey: "Re: limits of SQL Server???"
- Next in thread: Keith Kratochvil: "Re: limits of SQL Server???"
- Messages sorted by: [ date ] [ thread ]
Date: Sun, 15 Feb 2004 09:41:20 -0500
Mickey,
You can get the DDL for any object by right clicking on that object and
choosing "Generate SQL Script" (depending on where you are you may first
need to choose "All Tasks") in Enterprise Manager. Then you pick and choose
what you want with the script. For instance if you want the indexes and
triggers (which is always good to show). This allows someone else to create
a table exactly like yours or at least see what it is without guessing as to
what indexes or datatypes etc. As for your queries I have a few comments.
First off I would suggest you use stored procedures instead of generating
the code and issuing it from the front end. It will be easier to maintain
and potentially much more performant. These 2 queries should be a snap to
make into sp's (if you need help with that let me know). I see what might
be an issue with these in the WHERE clause. Both queries use a line similar
to this:
> WHERE tpar.GroupID " & CSQLEq(ELng(ParentGroup)) _
So if this is the case it looks like your missing an operator. Should there
be an "=" between the GroupID and the value?
To test these in QA just copy the sql and replace the variable with an
actual value to see how well the query performs just by itself. Then if
that is fine you can run profiler and run that query from the web page and
see if it is infact the same statement that you issued in QA. You will also
be able to see the duration of the query and then determine if it is sql
server or the front end that is taking all the time. It still would be nice
to see the DDL including the indexes for these tables and a count of how
many rows are in each.
--
Andrew J. Kelly
SQL Server MVP
"mickey" <newslist@REMOVEMEmicnap.com> wrote in message
news:e7neUQ28DHA.4044@tk2msftngp13.phx.gbl...
> Yes, I did ask this before. I was trying to ask it again in a different
way
> to hopefully narrow it down. I obviously don't know much about SQL
Server.
> I am still learning.
>
> >>>>> Just selecting 400 rows is nothing for sql server.
>
> Thank you. That's the answer I needed.
>
> >>>>>>But how you do it and what the DDL looks like can have a tremendous
> effect on the performance. Can you
> > post the DDL and actual query so we can see what we are dealing with?
>
> Well, I would but what is a DDL? Data Definition Language and it does the
> CREATE, DROP, ALTER, etc. stuff, I know. But what is it you want and how
do
> I get to it?
>
> There are 2 queries that are run in the subroutine to get the info to
> display it.
>
> strSQL = "SELECT tgrp.GroupID, tgrp.Name, tgrp.Descrip, tgrp.Img," _
> & " tpar.GroupID AS ParentID, tpar.Name AS ParentName" _
> & " FROM (tblGroups tgrp LEFT JOIN tblGroupsParents tgp ON tgrp.GroupID =
> tgp.GroupID)" _
> & " LEFT JOIN tblGroups tpar ON tgp.ParentID = tpar.GroupID" _
> & " WHERE tpar.GroupID " & CSQLEq(ELng(ParentGroup)) _
> & " AND tgrp.ShowIt <> 0" _
> & " ORDER BY tgrp.Name"
>
> strSQL = "SELECT COUNT(*) AS Total FROM tblInventoryGroups tinvg" _
> & " INNER JOIN tblInventory tinv ON tinvg.ItemID = tinv.ItemID" _
> & " WHERE tinvg.GroupID " & CSQLEq(rsG("GroupID")) _
> & " AND tinv.ShowIt <> 0"
>
> >>>>>Are you sure the time is on the sql side and not the asp side?
>
> No.
>
> >>>> How long does the query take if you run it straight from query
> analyzer?
>
> How do you run a query that contains a variable in the query analyzer?
>
> I took out the part of the WHERE clause that uses the variable and it
> returned results in less than a second.
>
> >>>>Your statement in that this is for people who know exactly what they
> want doesn't make
> > sense. If that is true why are you displaying all 400 items? Why not
> just
> > list the main categories and let them drill down into the one they want?
>
> Because the site already has the "drill down" feature and it's a pain in
the
> ass to go back to the top when you want to look at another category. By
> listing the categories, a person can click on one link to take them to the
> list and then another click on the list to take them to what they want.
> Otherwise they have to click the back button forever to get back to the
top.
> And plus it allows them to see at a glance all the categories instead of
> waiting for another page to come up to find that it's not what they
wanted.
>
> Thanks for replying,
> Mickey
>
>
> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
> news:uWKGil18DHA.696@tk2msftngp13.phx.gbl...
> > I thought you already asked this before but just in case here goes.
Just
> > selecting 400 rows is nothing for sql server. But how you do it and
what
> > the DDL looks like can have a tremendous effect on the performance. Can
> you
> > post the DDL and actual query so we can see what we are dealing with?
> Are
> > you sure the time is on the sql side and not the asp side? How long
does
> > the query take if you run it straight from query analyzer? Your
statement
> > in that this is for people who know exactly what they want doesn't make
> > sense. If that is true why are you displaying all 400 items? Why not
> just
> > list the main categories and let them drill down into the one they want?
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "mickey" <newslist@REMOVEMEmicnap.com> wrote in message
> > news:%23WTwbc18DHA.1504@TK2MSFTNGP12.phx.gbl...
> > > I am setting up an "expanded view" of all the categories of products
> > > available in a shopping cart system. The list pulls all the
categories
> > from
> > > the database in groups. See http://www.micnap.com/cart/tek9.asp.
This
> > page
> > > is really slow to load. I assume the problem is caused by trying to
> pull
> > > about 400 things from the database because when I switch to using a
> > database
> > > with only a few records, it's fine.
> > >
> > > So....my question is....
> > >
> > > What is the generally accepted limit of # of items that can be pulled
> from
> > > SQL Server without slowing down the page load? Am I just asking too
> much
> > > with 400 items?
> > >
> > > Thanks,
> > > Mickey
> > >
> > > P.S. I realize that it is pulling the 400 categories twice because
it's
> > > also putting them into a dropdown box in addition to the list but it's
> > still
> > > slow with just the list.
> > >
> > > P.S.S. And no, they do not want the list as their main navigation for
> > their
> > > shopping cart. The list is too long. They want it as an added
feature
> > for
> > > people that know exactly what they are looking for. The link is just
a
> > demo
> > > page.
> > >
> > >
> >
> >
>
>
- Next message: Mark Lang: "SQL Server 2000 Eval won't install"
- Previous message: bstevenson_at_agilera.com: "Re: Problems setting sqlagent proxy account"
- In reply to: mickey: "Re: limits of SQL Server???"
- Next in thread: Keith Kratochvil: "Re: limits of SQL Server???"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
