Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> дÈëÏûÏ¢ÐÂÎÅ
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> > treese
> > Do you have appropriate indexes for your table?
> > Using LIKE '%Something' may prevent from query optimizer to use an
index.
> >
> >
> >
> >
> > "treesy" <treesy@.hostran.com.cn> wrote in message
> > news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > > I have a table of 60,000rows. There are 47 fields in the table. 43 of
> them
> > > are nvarchar with length of 100. Two of them are big nvarchar with
> length
> > of
> > > 1024 and 2048. an indexno field is int and the other is date. two
> primary
> > > keys, one of them is indexno;the other is nvarchar.
> > >
> > > Low performance:
> > > 1.using query of 'like', it shows timeout. I have to use indexno to
> > control
> > > the number of the rows i am querying.
> > > 2.When I use application program to query some length of bytes, it
costs
> > > quite a lot of minutes.
> > > Any one can help?
> > >
> > > Thanks in advance.
> > >
> > >
> >
> >
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> дÈëÏûÏ¢ÐÂÎÅ
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> > I have a table of 60,000rows. There are 47 fields in the table. 43 of
them
> > are nvarchar with length of 100. Two of them are big nvarchar with
length
> of
> > 1024 and 2048. an indexno field is int and the other is date. two
primary
> > keys, one of them is indexno;the other is nvarchar.
> >
> > Low performance:
> > 1.using query of 'like', it shows timeout. I have to use indexno to
> control
> > the number of the rows i am querying.
> > 2.When I use application program to query some length of bytes, it costs
> > quite a lot of minutes.
> > Any one can help?
> >
> > Thanks in advance.
> >
> >
>

Problem: sql server 2000 server low performance. thanks:)

I have a table of 60,000rows. There are 47 fields in the table. 43 of them
are nvarchar with length of 100. Two of them are big nvarchar with length of
1024 and 2048. an indexno field is int and the other is date. two primary
keys, one of them is indexno;the other is nvarchar.
Low performance:
1.using query of 'like', it shows timeout. I have to use indexno to control
the number of the rows i am querying.
2.When I use application program to query some length of bytes, it costs
quite a lot of minutes.
Any one can help?
Thanks in advance.treese
Do you have appropriate indexes for your table?
Using LIKE '%Something' may prevent from query optimizer to use an index.
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Thanks! Uri Dimant
I have tried to set up index on those frequently used fiels. It doesn't
work. Any other clues?
treesy
"Uri Dimant" <urid@.iscar.co.il> д?
:eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
> treese
> Do you have appropriate indexes for your table?
> Using LIKE '%Something' may prevent from query optimizer to use an index.
>
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>|||Did you see that after adding these indexes the optimizer was able to use
them?
"treesy" <treesy@.hostran.com.cn> wrote in message
news:utAb5uT9DHA.2656@.TK2MSFTNGP11.phx.gbl...
> Thanks! Uri Dimant
> I have tried to set up index on those frequently used fiels. It doesn't
> work. Any other clues?
> treesy
> "Uri Dimant" <urid@.iscar.co.il> д?
> :eSKuQfS9DHA.3364@.TK2MSFTNGP09.phx.gbl...
index.
> them
> length
> primary
costs
>|||If your like clause begins with a wildcard, it is unlikely that SQL will use
the index(in an index seek), however it may do an index scan of the leaf
level...
Look at the graphical showplan to see which access method the optimizer is
choosing.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"treesy" <treesy@.hostran.com.cn> wrote in message
news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
> I have a table of 60,000rows. There are 47 fields in the table. 43 of them
> are nvarchar with length of 100. Two of them are big nvarchar with length
of
> 1024 and 2048. an indexno field is int and the other is date. two primary
> keys, one of them is indexno;the other is nvarchar.
> Low performance:
> 1.using query of 'like', it shows timeout. I have to use indexno to
control
> the number of the rows i am querying.
> 2.When I use application program to query some length of bytes, it costs
> quite a lot of minutes.
> Any one can help?
> Thanks in advance.
>|||Hi, thank you all!
I have solved the problem! I split my big table into 5 tables. The
performance is good this time.
"Wayne Snyder" <wsnyder@.computeredservices.com> д?
:ufDflpV9DHA.972@.tk2msftngp13.phx.gbl...
> If your like clause begins with a wildcard, it is unlikely that SQL will
use
> the index(in an index seek), however it may do an index scan of the leaf
> level...
> Look at the graphical showplan to see which access method the optimizer is
> choosing.
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "treesy" <treesy@.hostran.com.cn> wrote in message
> news:#cCL$9R9DHA.1804@.TK2MSFTNGP12.phx.gbl...
them
length
> of
primary
> control
>

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

problem with varchar and nvarchar datatype in linked server

Hi,

I am updating a remote table using linked server in sql server 2005.

but in case of varchar and nvarchar i am getting an error :

"OLE DB provider "SQLNCLI" for linked server "LinkedServer1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

Msg 16955, Level 16, State 2, Line 1

Could not create an acceptable cursor."

thanks in advance.

Thanks & Regards

Pintu

Pintu, I had a similar problem with deleting rows through a linked sever from SQL Server 2005. I tried from SQL Server 2000 and got a slightly different error :-

"The provider could not support a row lookup position. The provider indicates that conflicts occurred with other properties or requirements."

which led me to this article:-

http://support.microsoft.com/kb/814581

which solved my problem. Hope it helps you too.

Problem with UPDATE with Detailsview

i get

Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@.BookingID".

Description:Anunhandled exception occurred during the execution of the current webrequest. Please review the stack trace for more information about theerror and where it originated in the code.

Exception Details:System.Data.SqlClient.SqlException: Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@.BookingID".

Source Error:

An unhandled exception was generated during the execution of thecurrent web request. Information regarding the origin and location ofthe exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Incorrect syntax near 'nvarchar'.
Must declare the scalar variable "@.BookingID".]
 System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +857354
 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734966
 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188

...

i am using the Edit feature of the detailsview, seems like the datatype is wrong? ID are int (in my DB, BookingID is int), and also its readonly it should not be updating in the first place right?

----------

Any way to debug whats in "new" values? ItemUpdating? how to do it?

Can you post your code?

|||

erm... its SqlDataSource controls + DetailsView, shouldn't it be automatic? i found out that some tables can work some cannot

anyway

UPDATE [Booking]SET [InventoryID] = @.InventoryID, [Username] = @.Username, [Status] = @.Status, [DateBooked] = @.DateBookedWHERE [BookingID] = @.BookingIDSELECT Booking.BookingID, Booking.InventoryID, Booking.Username, Booking.Status, Booking.DateBooked, InventoryPC.TitleAS [Inventory Title]FROM BookingINNERJOIN InventoryPCON Booking.InventoryID = InventoryPC.InventoryIDWHERE (Booking.BookingID = @.ID)

the code from data source the details view is getting from... looks like thats all relavent code? what else is needed,|||

It would be best if you post all your code (page and code behind).

problem with Update command with parameters

Hi,

i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.
This happens in event DetailsView1_ItemUpdating with my own code.
It works without parameters (i know, bad way) like this:
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"

But when using parameters like here below, i get the error:
"Input string was not in a correct format"

Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles
DetailsView1.ItemUpdating
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= @.myname"
SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, na)
SqlDataSource1.Update()
End Sub

I don't see what's wrong here.
Thanks for help
Tartuffe

Try the following:

SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, 5).value = na

|||

Set the updatecommand and parameter types in the sqldatasource.

In the detailsview.Updating event, you can play with the e.NewValues, e.OldValues collections to make any changes you want.

Or you can make your changes in the sqldatasource.updating event and play with the parameters directly via e.Command.Parameters.

Much cleaner.

|||

Thanks to reply.

I tried this:

SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, 5).value = na

but in the Intellisense, 'value' doesn't appear, and when i force it in the code, i ger the strange error:

'value' is not a memeber of 'integer' ...

|||

Hi tartuffe2,

SqlDataSource.UpdateParameters.Add() will return the index value of the added item in your Sql Datasource Parameter Collection, thus you cannot assign a new value to it.

As to add a new value into a parameter collection, i think your origional way is correct ( SqlDataSource.UpdateParameters.Add("@.parametername",SqlDBType.NVChar,na) ). I cannot see any errors in the code you origionally posted. Have you tried the methodMotley suggested? That maybe is able to solve your problems. thanks

problem with update command

Hi,

i try to update field 'name' (nvarchar(5) in sql server) of table 'mytable'.
This happens in event DetailsView1_ItemUpdating with my own code.
It works without parameters (i know, bad way) like this:
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= '" & na & "'"

But when using parameters like here below, i get the error:
"Input string was not in a correct format"

Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles
DetailsView1.ItemUpdating
SqlDataSource1.UpdateCommand = "UPDATE mytable set name= @.myname"
SqlDataSource1.UpdateParameters.Add("@.myname", SqlDbType.NVarChar, na)
SqlDataSource1.Update()
End Sub

I don't see what's wrong here.
Thanks for help
Tartuffe

Hi There,

Try exclude the'@.' in your add("@.myname"...

Example:

SqlDataSource1.UpdateCommand = "UPDATE mytable set name= @.myname"
SqlDataSource1.UpdateParameters.Add("myname", SqlDbType.NVarChar, na)

Hope it helps!

|||

thank you, that's the solution.