optimization question

I have a table with a large amount of rows > 100 000 with
ID|NAME|HITScolumns
ID is the primary key
NAME is nvarchar
HITS is int
I run this query, it takes less than a second
select top 40 * from table 1 order by id desc
but when i run this it take more than 10 seconds
select top 40 * from table 1 order by hits desc
hits column keeps track of time the page has been loaded
what can i do to make the second query run just as fast as the first one?Do you have an index on HIts? If not then you might want to add one.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>I have a table with a large amount of rows > 100 000 with
> ID|NAME|HITScolumns
> ID is the primary key
> NAME is nvarchar
> HITS is int
> I run this query, it takes less than a second
> select top 40 * from table 1 order by id desc
> but when i run this it take more than 10 seconds
> select top 40 * from table 1 order by hits desc
>
> hits column keeps track of time the page has been loaded
> what can i do to make the second query run just as fast as the first one?
>
>|||what type of index would you recommend? I ran the Database Engine Tuning
Advisor it didn't give me anything
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
> Do you have an index on HIts? If not then you might want to add one.
> --
> Andrew J. Kelly SQL MVP
>
> "Howard" <howdy0909@.yahoo.com> wrote in message
> news:%23hSBkKSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>|||You really only have two choices here. A clustered or a Nonclustered index.
I assume you already have a clustered index on ID. So try adding a
non-clustered index on Hits and see if it helps.
Andrew J. Kelly SQL MVP
"Howard" <howdy0909@.yahoo.com> wrote in message
news:e$EfRUSUGHA.5500@.TK2MSFTNGP12.phx.gbl...
> what type of index would you recommend? I ran the Database Engine Tuning
> Advisor it didn't give me anything
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:u0g2bRSUGHA.5552@.TK2MSFTNGP14.phx.gbl...
>

OPENXML for bulk inserts

Hi and thk for your help ;-)
I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
@.xmlOrders nText,
@.var_id int
I have this xml (@.xmlOrders ) :
<ORDER>
<ORDER>
<art_desc>blablablabla.</art_desc>
<art_code>1</art_code>
<art_units>50</art_units>
<xx>111</xx>
<yy>111</yy>
</ORDER>
<ORDER>
<art_desc>tetetetet.</art_desc>
<art_code>2</art_code>
<art_units>10</art_units>
<xx>222</xx>
<yy>222</yy>
</ORDER>
</ORDER>
I need to insert the parameter @.var_id and this fields from @.xmlOrders:
(art_desc,art_code and art_units) into a table "tbl_orders" with this
structure:
order_id int identity
art_desc varchar
art_code varchar
art_units int
var_id int
How can i modify this for work:
DECLARE @.hDoc int
exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
Insert Into TBL_ORDERS
SELECT art_desc,art_code,art_units
FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
WITH (art_desc varchar(100), art_code varchar(100),art_units int)
XMLOrders
EXEC sp_xml_removedocument @.hDoc
Thank you.
Hello, Oterox!
You wrote on Thu, 28 Oct 2004 15:04:20 +0200:
[Sorry, skipped]
O> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
The third parameter is the code for default mapping.
1 - attribute centerinc
2 - element centeric
Since you didn't point out the column pattern
O> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
the server use default, etc attribute centerinc mapping. This is not
correct, 'cause you don't have art_desc attribute as well as art_code and
art_units. To make this work you should change the default mapping to
element mapping:
FROM OPENXML (@.hdoc, '/ORDER/ORDER',2) --change the value to 2
or use explicit column mapping
FROM OPENXML (@.hdoc, '/ORDER/ORDER')
WITH(
art_desc varchar(100) 'art_desc',
art_code varchar(100) 'art_code',
art_units int 'art_units'
)
With best regards, Alex Shirshov.
|||If you could send me your procedure and your xml.file.And write me how you
import xml file to sql database.
my e-mail: ljag@.wp.pl
Uytkownik "Oterox" <oterox@.asp404.com> napisa w wiadomoci
news:u0EcT7OvEHA.3200@.TK2MSFTNGP14.phx.gbl...
> Hi and thk for your help ;-)
> I'm writing a stored procedure for bulk inserts.The sp have 2 parameters:
> @.xmlOrders nText,
> @.var_id int
> I have this xml (@.xmlOrders ) :
> <ORDER>
> <ORDER>
> <art_desc>blablablabla.</art_desc>
> <art_code>1</art_code>
> <art_units>50</art_units>
> <xx>111</xx>
> <yy>111</yy>
> </ORDER>
> <ORDER>
> <art_desc>tetetetet.</art_desc>
> <art_code>2</art_code>
> <art_units>10</art_units>
> <xx>222</xx>
> <yy>222</yy>
> </ORDER>
> </ORDER>
> I need to insert the parameter @.var_id and this fields from @.xmlOrders:
> (art_desc,art_code and art_units) into a table "tbl_orders" with this
> structure:
> order_id int identity
> art_desc varchar
> art_code varchar
> art_units int
> var_id int
> How can i modify this for work:
> DECLARE @.hDoc int
> exec sp_xml_preparedocument @.hDoc OUTPUT,@.xmlOrders
> Insert Into TBL_ORDERS
> SELECT art_desc,art_code,art_units
> FROM OPENXML (@.hdoc, '/ORDER/ORDER',1)
> WITH (art_desc varchar(100), art_code varchar(100),art_units int)
> XMLOrders
> EXEC sp_xml_removedocument @.hDoc
> Thank you.
>
>