Link server very slow

I created a link server to 'oracle', properties is below
Server Type : Microsoft OLE DB Provider for Oracle
Product name : ora
Data Source : sapdbproduction
Provider string : MSDAORA
Compare query response time (1 record)
PL-SQL : 0.5 sec
SQL Query Analyzer : 1.4 minute
How can i do?
Thanks in adv.
Hi
Post the DDL and DML.
Regards
Mike
"teera" wrote:

> I created a link server to 'oracle', properties is below
> Server Type : Microsoft OLE DB Provider for Oracle
> Product name : ora
> Data Source : sapdbproduction
> Provider string : MSDAORA
> Compare query response time (1 record)
> PL-SQL : 0.5 sec
> SQL Query Analyzer : 1.4 minute
> How can i do?
> Thanks in adv.
>
>
|||Mike
What is DDL and DML? Please explain me more?
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...[vbcol=seagreen]
> Hi
> Post the DDL and DML.
> Regards
> Mike
> "teera" wrote:
|||DDL = Data Definition Language. It's basically the schema of the
associated DB objects (table(s), index(es), view(s), trigger(s), etc.).
DML = Data Manipulation Language. It's the queries that you're running
against those DB objects (SELECT, INSERT, UPDATE, DELETE). The queries
about which you say the response time through the linked server is slow.
Basically, nobody can help you if you don't give any details about the
problem. To help we'll need to see things like execution plans,
indexing of search arguments, etc. We can't do that if we don't know
what your schema looks like and what query you're trying to execute
against that schema. It's possible that the entire table data is being
sent from the Oracle box to the SQL box (through the linked server) and
then any WHERE clause is being applied (which would explain the slow
performance through the link but not direct to Oracle) but we can't tell
if you don't show us the schema & queries involved.
Cheers,
Mike.
teera wrote:
> Mike
> What is DDL and DML? Please explain me more?
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...
>
>
C

Link server very slow

I created a link server to 'oracle', properties is below
Server Type : Microsoft OLE DB Provider for Oracle
Product name : ora
Data Source : sapdbproduction
Provider string : MSDAORA
Compare query response time (1 record)
PL-SQL : 0.5 sec
SQL Query Analyzer : 1.4 minute
How can i do?
Thanks in adv.Hi
Post the DDL and DML.
Regards
Mike
"teera" wrote:

> I created a link server to 'oracle', properties is below
> Server Type : Microsoft OLE DB Provider for Oracle
> Product name : ora
> Data Source : sapdbproduction
> Provider string : MSDAORA
> Compare query response time (1 record)
> PL-SQL : 0.5 sec
> SQL Query Analyzer : 1.4 minute
> How can i do?
> Thanks in adv.
>
>|||Mike
What is DDL and DML? Please explain me more?
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...[vbcol=seagreen]
> Hi
> Post the DDL and DML.
> Regards
> Mike
> "teera" wrote:
>|||DDL = Data Definition Language. It's basically the schema of the
associated DB objects (table(s), index(es), view(s), trigger(s), etc.).
DML = Data Manipulation Language. It's the queries that you're running
against those DB objects (SELECT, INSERT, UPDATE, DELETE). The queries
about which you say the response time through the linked server is slow.
Basically, nobody can help you if you don't give any details about the
problem. To help we'll need to see things like execution plans,
indexing of search arguments, etc. We can't do that if we don't know
what your schema looks like and what query you're trying to execute
against that schema. It's possible that the entire table data is being
sent from the Oracle box to the SQL box (through the linked server) and
then any WHERE clause is being applied (which would explain the slow
performance through the link but not direct to Oracle) but we can't tell
if you don't show us the schema & queries involved.
Cheers,
Mike.
teera wrote:
> Mike
> What is DDL and DML? Please explain me more?
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...
>
>
>
C

Link server very slow

I created a link server to 'oracle', properties is below
Server Type : Microsoft OLE DB Provider for Oracle
Product name : ora
Data Source : sapdbproduction
Provider string : MSDAORA
Compare query response time (1 record)
PL-SQL : 0.5 sec
SQL Query Analyzer : 1.4 minute
How can i do?
Thanks in adv.Hi
Post the DDL and DML.
Regards
Mike
"teera" wrote:
> I created a link server to 'oracle', properties is below
> Server Type : Microsoft OLE DB Provider for Oracle
> Product name : ora
> Data Source : sapdbproduction
> Provider string : MSDAORA
> Compare query response time (1 record)
> PL-SQL : 0.5 sec
> SQL Query Analyzer : 1.4 minute
> How can i do?
> Thanks in adv.
>
>|||Mike
What is DDL and DML? Please explain me more?
Thanks
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...
> Hi
> Post the DDL and DML.
> Regards
> Mike
> "teera" wrote:
>> I created a link server to 'oracle', properties is below
>> Server Type : Microsoft OLE DB Provider for Oracle
>> Product name : ora
>> Data Source : sapdbproduction
>> Provider string : MSDAORA
>> Compare query response time (1 record)
>> PL-SQL : 0.5 sec
>> SQL Query Analyzer : 1.4 minute
>> How can i do?
>> Thanks in adv.
>>|||DDL = Data Definition Language. It's basically the schema of the
associated DB objects (table(s), index(es), view(s), trigger(s), etc.).
DML = Data Manipulation Language. It's the queries that you're running
against those DB objects (SELECT, INSERT, UPDATE, DELETE). The queries
about which you say the response time through the linked server is slow.
Basically, nobody can help you if you don't give any details about the
problem. To help we'll need to see things like execution plans,
indexing of search arguments, etc. We can't do that if we don't know
what your schema looks like and what query you're trying to execute
against that schema. It's possible that the entire table data is being
sent from the Oracle box to the SQL box (through the linked server) and
then any WHERE clause is being applied (which would explain the slow
performance through the link but not direct to Oracle) but we can't tell
if you don't show us the schema & queries involved.
Cheers,
Mike.
teera wrote:
> Mike
> What is DDL and DML? Please explain me more?
> Thanks
> "Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
> news:68EECFB8-4739-4277-A78A-A21F3F6CA33B@.microsoft.com...
>>Hi
>>Post the DDL and DML.
>>Regards
>>Mike
>>"teera" wrote:
>>
>>I created a link server to 'oracle', properties is below
>> Server Type : Microsoft OLE DB Provider for Oracle
>> Product name : ora
>> Data Source : sapdbproduction
>> Provider string : MSDAORA
>>Compare query response time (1 record)
>> PL-SQL : 0.5 sec
>> SQL Query Analyzer : 1.4 minute
>>How can i do?
>>Thanks in adv.
>>
>
>
--
ÿþC

linefeeds in text fields extracted with substring

Im running a query like
select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
MyColumn is th the text type.
The text in MyColumn contains a lot of tabulator characters (TAB),
carrige return (CR) and linefeed (LF) characters.
Sometimes (but not often) when I run the query, even in the Query
Analyser, I get the wrong result. I get too few characters, even though
I know they are in the database. (If I read the field with out the
substring, I can see everything).
The error is consistent - either a record works as it should, or it
fails to.
All answers are very welcome.
Thomas K
select @.@.version
----
----
----
--
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)I think you did not handle the Tab, CR and Linefeed. The records that
dont have these will work otherwise fail
Madhivanan|||Check your 'maximum characters per column' setting in Query Analyzer under
Tools --> Options --> Results. This may be set too small and truncate the
value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Holme" <seiofecco@.hotmail.com> wrote in message
news:ONdDTThJFHA.1096@.tk2msftngp13.phx.gbl...
> Im running a query like
> select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
> MyColumn is th the text type.
> The text in MyColumn contains a lot of tabulator characters (TAB), carrige
> return (CR) and linefeed (LF) characters.
> Sometimes (but not often) when I run the query, even in the Query
> Analyser, I get the wrong result. I get too few characters, even though I
> know they are in the database. (If I read the field with out the
> substring, I can see everything).
> The error is consistent - either a record works as it should, or it fails
> to.
>
> All answers are very welcome.
> Thomas K
>
> select @.@.version
> ----
----
----
--
--
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> (1 row(s) affected)sql

Limited length of text data type

I cannot put the very long long text into the fields that
have text data type how can I extends the length of this
data type?
Thanks
Hi,
Did you meant that you got problems while retrieval. If it is a problem with
retrieval then use the below commands:-
SET TEXTSIZE { number }
select @.@.textsize
See books online for more details.
Thanks
Hari
MCDBA
"kwan" <anonymous@.discussions.microsoft.com> wrote in message
news:9d2e01c48675$7273bde0$a601280a@.phx.gbl...
> I cannot put the very long long text into the fields that
> have text data type how can I extends the length of this
> data type?
> Thanks
|||How are you trying to "put" the very long long text? Have you tried an
INSERT statement in Query Analyzer or from an application?
Are you sure the problem is in inserting the data, and not in
retrieving/viewing it?
http://www.aspfaq.com/
(Reverse address to reply.)
"kwan" <anonymous@.discussions.microsoft.com> wrote in message
news:9d2e01c48675$7273bde0$a601280a@.phx.gbl...
> I cannot put the very long long text into the fields that
> have text data type how can I extends the length of this
> data type?
> Thanks

Limitayion of indexes

Hello there
I've been heard that after amount of data on table (Approx 1,000,000 records
and more) according to the type of index, the index performance is become
mutch more slower.
Does someone knows what is the limitation and how can i handle of it?Roy,shalom
It depends on how often your table is acceseed (insert/update/deleted) .
What is a WHERE condion and how often is changed. I'd prefer to rebuild
indexes on all my user tables one a w.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23Cxk3h$SGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I've been heard that after amount of data on table (Approx 1,000,000
> records and more) according to the type of index, the index performance is
> become mutch more slower.
> Does someone knows what is the limitation and how can i handle of it?
>|||Whell Uri
Have you ever worked with approx 10,000,000 records?
and i know that there is fill factor for this case.
Do you have any idea for what is use?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23HcZEl$SGHA.5828@.TK2MSFTNGP14.phx.gbl...
> Roy,shalom
> It depends on how often your table is acceseed (insert/update/deleted) .
> What is a WHERE condion and how often is changed. I'd prefer to rebuild
> indexes on all my user tables one a w.
>
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%23Cxk3h$SGHA.6084@.TK2MSFTNGP14.phx.gbl...
>|||Roy
> Have you ever worked with approx 10,000,000 records?
:-))) Yes , even much much more

> and i know that there is fill factor for this case.
Yes , FillFactor is created along with a creation of index on the table

> Do you have any idea for what is use?
Do you want to know what FILLFACTOR to put on the table?
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:O%23Rwtu$SGHA.5736@.TK2MSFTNGP10.phx.gbl...
> Whell Uri
> Have you ever worked with approx 10,000,000 records?
> and i know that there is fill factor for this case.
> Do you have any idea for what is use?
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23HcZEl$SGHA.5828@.TK2MSFTNGP14.phx.gbl...
>|||Index fragmentation can increase the amount of I/O required to scan an
index, and this would be more likely and noticable on large tables. DBCC
SHOWCONTIG will reveal the amount of fragmentation and DBCC DBREINDEX or
DBCC INDEXDEFRAG can be occasionally run the correct the problem, which
typically accumulates over time.
Understanding SQL Server's DBCC SHOWCONTIG:
http://www.sql-server-performance.c..._showcontig.asp
Microsoft SQL Server 2000 Index Defragmentation Best Practices:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%23Cxk3h$SGHA.6084@.TK2MSFTNGP14.phx.gbl...
> Hello there
> I've been heard that after amount of data on table (Approx 1,000,000
> records and more) according to the type of index, the index performance is
> become mutch more slower.
> Does someone knows what is the limitation and how can i handle of it?
>

limit the number of digits after decimal point in flaot data type

Hi..

I have a column in the data base with the type Float,

I want to limit the number of digits after decimal point to 2 when I display the value in ASP.NET but I don't know how!?

the number that appear after calculation llike "93.333333"

I use decimal(2,2) as data type but an error accour and this is the message

"- Unable to modify table.
Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."

Can you help me..

thanks

Hi..

Before saving to database you can use String.Format() method to modify your float value.

First you need to convert float into string using

String myStr = Convert.ToString(yourFloatHere);

And then use String.Format("{0:N}", myStr);

and then again convert it into float using COnvert.ToDouble(MyStr);

Cheers n Hope it would help

|||

Hi..

I try what you suggest but the following error produced:

Cannot implicitly convert type 'double' to 'float'. An explicit conversion exists (are you missing a cast?)

Do you konw its solution?!

This is my code:

//------------------

float temp;

String value;

SqlConnection con =newSqlConnection(@."Data Source=localhost ;Initial Catalog=university ;Integrated Security=True");

con.Open();

SqlCommand cmd =newSqlCommand("select SUM(FinalGrid)/COUNT(FinalGrid)from studentSections Where studentSection_UserID='" + Session["ID"].ToString() +"'", con);

SqlDataReader reader = cmd.ExecuteReader();

while (reader.Read())

{

value =Convert.ToString(reader[0]);

}

String.Format("{0:2}", value);temp =Convert.ToDouble(value); //Here is the error

reader.Close();

//------------------------

Is it has any wrong?!

|||

Do not use the decimal(2,2). Just have the way it is now and just modify the SQL statment that you use to retrive the data and include the function

STR(columnname, lenght, number of decimal places)

This function will automatically round the data in this column to number of decimal places u want when the data is returned to ur ASP.NET application.

|||

Hi..

Can you tell me where I should use this function? and what do you mean by "length" ?

|||

Suppose you are using the query "select column1 from table1" to get the data to your applicaiton, where column1 is the one where you want only 2 decimals

u will have to use it as "select str(column1,10,2) as column1 from table1"

this will make sure you have only 2 decimals in the data that is returned, if there are more than 2 , it will round it to 2 decimals.

|||

Thanks so much its work very nice

Limit on size of text parameter type in stored procedures?

Hi,
I use stored procedures in combination with openxml. XML text is sent to the
stored procedures as a "text" type parameter.
Is there a limit on the size that this can be? I intend to pass very large
XML documents so its important for us that there is no size limit.
Thanks!
The size limit of text and ntext parameters is 2GB. If you show me an XML
document of that size, I will show you with high probability a design
mistake :-).
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I use stored procedures in combination with openxml. XML text is sent to
> the
> stored procedures as a "text" type parameter.
> Is there a limit on the size that this can be? I intend to pass very large
> XML documents so its important for us that there is no size limit.
> Thanks!
>
|||That's ample! Our files range from 20mb to 100mb. We are planning to make
extensive use of stored procedures and OPENXML - so I just wanted to check
SQL Server would cope with our requirements.
Thanks a lot for the info!!!!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> The size limit of text and ntext parameters is 2GB. If you show me an XML
> document of that size, I will show you with high probability a design
> mistake :-).
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
large
>
|||Note that if you are using 20mb to 100mb documents with OpenXML, that you
will have to make sure that your SQL Server instance will have at least x mb
(size of doc) * 4 (avg overhead of DOM) * 8 (1/8th usage limit for OpenXML
in SQL Server) memory available to get acceptable performance. For such
large documents in SQL Server 2000, we normally recommend to use the SQLXML
Bulkload object instead of OpenXML.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:e4Ln1CJKFHA.2524@.TK2MSFTNGP10.phx.gbl...
> That's ample! Our files range from 20mb to 100mb. We are planning to make
> extensive use of stored procedures and OPENXML - so I just wanted to check
> SQL Server would cope with our requirements.
> Thanks a lot for the info!!!!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...
> large
>

Limit on size of text parameter type in stored procedures?

Hi,
I use stored procedures in combination with openxml. XML text is sent to the
stored procedures as a "text" type parameter.
Is there a limit on the size that this can be? I intend to pass very large
XML documents so its important for us that there is no size limit.
Thanks!The size limit of text and ntext parameters is 2GB. If you show me an XML
document of that size, I will show you with high probability a design
mistake :-).
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I use stored procedures in combination with openxml. XML text is sent to
> the
> stored procedures as a "text" type parameter.
> Is there a limit on the size that this can be? I intend to pass very large
> XML documents so its important for us that there is no size limit.
> Thanks!
>|||That's ample! Our files range from 20mb to 100mb. We are planning to make
extensive use of stored procedures and OPENXML - so I just wanted to check
SQL Server would cope with our requirements.
Thanks a lot for the info!!!!
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...
> The size limit of text and ntext parameters is 2GB. If you show me an XML
> document of that size, I will show you with high probability a design
> mistake :-).
> Best regards
> Michael
> "Xerox" <anon@.anon.com> wrote in message
> news:eJAPNtVJFHA.2604@.TK2MSFTNGP15.phx.gbl...
large
>|||Note that if you are using 20mb to 100mb documents with OpenXML, that you
will have to make sure that your SQL Server instance will have at least x mb
(size of doc) * 4 (avg overhead of DOM) * 8 (1/8th usage limit for OpenXML
in SQL Server) memory available to get acceptable performance. For such
large documents in SQL Server 2000, we normally recommend to use the SQLXML
Bulkload object instead of OpenXML.
Best regards
Michael
"Xerox" <anon@.anon.com> wrote in message
news:e4Ln1CJKFHA.2524@.TK2MSFTNGP10.phx.gbl...
> That's ample! Our files range from 20mb to 100mb. We are planning to make
> extensive use of stored procedures and OPENXML - so I just wanted to check
> SQL Server would cope with our requirements.
> Thanks a lot for the info!!!!
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eVeZJ2fJFHA.656@.TK2MSFTNGP14.phx.gbl...
> large
>