Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer 2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, ( which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I missing something?
Thanks in advance
How exactly are you populating the resultset? Do you see the same behavior
from a standalone JDBC program? Below is a code snippet that connects to
Northwind database. It retrieves the data without skipping the first row:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
con = java.sql.DriverManager.getConnection(url, userName, password);
String query = "SELECT * from Customers";
//Option 1
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
//Option 2
//Statement stmt = con.createStatement();
//ResultSet rs = stmt.executeQuery(query);
while(rs.next())
{
System.out.println("Contact Name: " + rs.getString("ContactName"));
}
rs.close();
stmt.close();
con.close();
con=null;
Give this code a try to see if the behavior is the same. Do you see the
same behavior with JDBC SP2?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| thread-index: AcQiWoPDPDw+LtmiRSmrZYKbSsks1w==
| X-WN-Post: microsoft.public.sqlserver.jdbcdriver
| From: "=?Utf-8?B?U3VtYUpEQkM=?=" <anonymous@.discussions.microsoft.com>
| Subject: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| Date: Wed, 14 Apr 2004 12:56:02 -0700
| Lines: 42
| Message-ID: <F9963AEF-09BA-428A-B5C9-12505D15AE9F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5891
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer
2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, (
which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the
cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and
the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I
missing something?
Thanks in advance
|
sql
Showing posts with label writing. Show all posts
Showing posts with label writing. Show all posts
Wednesday, March 28, 2012
Friday, March 9, 2012
Refer to ROWGUID col in update trigger
When writing an Update trigger on a table that has a UniqueIdentifier as the
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>
primary key, how to I refer to the current row being updated.
I am used to using @.@.identity when working with primary keys that are of
type int but a UniqueIdentifier cannot be an identity column. I suspect it
has something to do with the "Is ROWGUID" property but I can't find the
function for getting the GUID of the row being updated.
Thanks,
Andrew.There isn't one. If you need to know this then you should generate the Guid
beforehand and use it in the insert statement.
--
Andrew J. Kelly
SQL Server MVP
"Andrew" <sql@.ses.ca> wrote in message
news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> When writing an Update trigger on a table that has a UniqueIdentifier as
the
> primary key, how to I refer to the current row being updated.
> I am used to using @.@.identity when working with primary keys that are of
> type int but a UniqueIdentifier cannot be an identity column. I suspect
it
> has something to do with the "Is ROWGUID" property but I can't find the
> function for getting the GUID of the row being updated.
> Thanks,
> Andrew.
>|||I want to access the row that is being updated, not inserted. The GUID is
already there.
Does this mean it is not possible to use a trigger to update a
"DateOfLastUpdate" field if the primary key is a GUID? I realize date could
be updated if the update was performed using a stored procedure but I feel
it would be safer to have this functionality on the table itself.
Thanks,
Andrew
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> There isn't one. If you need to know this then you should generate the
Guid
> beforehand and use it in the insert statement.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > When writing an Update trigger on a table that has a UniqueIdentifier as
> the
> > primary key, how to I refer to the current row being updated.
> >
> > I am used to using @.@.identity when working with primary keys that are of
> > type int but a UniqueIdentifier cannot be an identity column. I suspect
> it
> > has something to do with the "Is ROWGUID" property but I can't find the
> > function for getting the GUID of the row being updated.
> >
> > Thanks,
> > Andrew.
> >
> >
>|||> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
I tend to use a wrapper like:
IF NOT UPDATE(ModDate)
Just to prevent recursion. :-)|||Thanks for your help. I had just "discovered" the inserted table but the
method I came up with is not as clean as yours.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uiAkt8iRDHA.3236@.TK2MSFTNGP10.phx.gbl...
> You threw me off when you mentioned @.@.IDENTITY. @.@.IDENTITY has nothing to
> do with an UPDATE, it is only useful for Inserts. Any time you want to
> reference a row that is being updated in a trigger you can use the
Inserted
> table. To update a datetime column you would simply do this:
> UPDATE YourTable SET ModDate = GETDATE()
> WHERE PK IN (SELECT i.PK FROM Inserted as i)
> This way it works when more than 1 rows is updated and there is no need to
> know what the actual value of the PK is.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Andrew" <sql@.ses.ca> wrote in message
> news:u53MXdiRDHA.2424@.tk2msftngp13.phx.gbl...
> > I want to access the row that is being updated, not inserted. The GUID
is
> > already there.
> >
> > Does this mean it is not possible to use a trigger to update a
> > "DateOfLastUpdate" field if the primary key is a GUID? I realize date
> could
> > be updated if the update was performed using a stored procedure but I
feel
> > it would be safer to have this functionality on the table itself.
> >
> > Thanks,
> > Andrew
> >
> >
> > "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> > news:%237WJLGiRDHA.1552@.TK2MSFTNGP10.phx.gbl...
> > > There isn't one. If you need to know this then you should generate
the
> > Guid
> > > beforehand and use it in the insert statement.
> > >
> > > --
> > >
> > > Andrew J. Kelly
> > > SQL Server MVP
> > >
> > >
> > > "Andrew" <sql@.ses.ca> wrote in message
> > > news:u3lnbAiRDHA.2636@.TK2MSFTNGP10.phx.gbl...
> > > > When writing an Update trigger on a table that has a
UniqueIdentifier
> as
> > > the
> > > > primary key, how to I refer to the current row being updated.
> > > >
> > > > I am used to using @.@.identity when working with primary keys that
are
> of
> > > > type int but a UniqueIdentifier cannot be an identity column. I
> suspect
> > > it
> > > > has something to do with the "Is ROWGUID" property but I can't find
> the
> > > > function for getting the GUID of the row being updated.
> > > >
> > > > Thanks,
> > > > Andrew.
> > > >
> > > >
> > >
> > >
> >
> >
>
Monday, February 20, 2012
Reduce Disk I/O when writing to MS-SQL Server database
Hi All,
An application is writing to the SQL Server database for every few
millisec. The disk I/O LED is continuously glowing when writing to the
database. Is there a problem with the configuration of the database ?.
How do i reduce the disk i/o when writing to the database. the
application is writing to the database using the 'insert' sql statement.Hi
Just a guess.
Check out that dusring the inserting you don't have page splits.
I think you have RAID5 disk configuration which is probably not the best
choice for SQL server because it imposes
a significant IO hit on write performance (both data and parity info must be
written)
<vanisathish@.gmail.com> wrote in message
news:1125888842.735920.102570@.z14g2000cwz.googlegroups.com...
> Hi All,
> An application is writing to the SQL Server database for every few
> millisec. The disk I/O LED is continuously glowing when writing to the
> database. Is there a problem with the configuration of the database ?.
> How do i reduce the disk i/o when writing to the database. the
> application is writing to the database using the 'insert' sql statement.
>|||I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
Harddisk. There are so many other threads running on the system. And
the application that writes to the sql database use the 'insert' sql
statement. do i need to use SQL stored procedure for this ?
Normally what is the approach that will be used to write to the sql
database from a VC++ application using ODBC|||Hi
SQL Server's perfomance will benefit from using stored procedures instead of
'insert' statements from the client
http://www.sql-server-performance.c..._procedures.asp
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Every transaction need to do physical I/O, otherwise, you couldn't trust the
data to be persisted
when you do commit. You can reduce the amount of I/O by combining several mo
difications in one
transaction, but you also need to consider transaction semantics as well as
blocking.
Make sure you don't have ODBC tracing turned on, btw...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||How much data are you writing? And how long does it take? Adding another
drive might help if you put the log on it, but running heavy operations on a
single processor, single disk, low memory system with lots of other stuff
running is not likely to run better. This can be made even worse if you
happen to have a very fragmented disk or (and this is the killer) you need
more than a GB of RAM and you have to do virtual memory paging.
As far as stored procedures, if you are doing many insert statements, a
stored procedure might help, but if it is just a single statement taking a
long time, then it is not likely.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Actually i am not writing to much of data to the database. I will be
writing around 50 - 100 bytes of data to 9 tables. This writing happens
very often to some of the tables of the order of few millisec.
Currently i'm running a IIS web-server and some other application
servers which continuously polls some devices for every 250 msec
I see the harddisk LED glowing continuously, when the data logging is
ON ie. when writing to database happens.
As i said earlier the desktop PC is having 1GB of RAM and 60 GB
harddisk and is running win XP
I'm thinking of running the database server on a separate PC. Will that
solve my problem ?|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
>Actually i am not writing to much of data to the database. I will be
>writing around 50 - 100 bytes of data to 9 tables. This writing happens
>very often to some of the tables of the order of few millisec.
SQLServer will insist on doing at least TWO physical writes to the
disk for every transaction, one for the log, the other for the real
database.
>Currently i'm running a IIS web-server and some other application
>servers which continuously polls some devices for every 250 msec
>I see the harddisk LED glowing continuously, when the data logging is
>ON ie. when writing to database happens.
Try using perfmon to get more accurate data, the LED stays on long
enough for you to see, even if the disk is busy only for a fraction of
that time.
>As i said earlier the desktop PC is having 1GB of RAM and 60 GB
>harddisk and is running win XP
Best SQLServer performance requires putting the log on a separate
physical disk, perhaps you begin to see why!
>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
It may, but you should also consider getting a big, fat server-class
machine and not a desktop.
You might also consider writing some middleware that accumulates your
data for a couple of seconds at a time before writing it.
You haven't said anything about the schema of the tables you're
writing to, do you mean you always write to 9 tables? Do they have
PKs? What kind of PKs? Any other indexes? Any other DRI?
All that said, what the heck, let the little light stay on, that's
what it's there for!
But again, get some better numbers with perfmon and profiler.
J.|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
(snip)
>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
Hi vanisathish,
What exactly IS your problem? The only thing I've seen so far is that
your Disk I/O LED is glowing continuously - frankly, I'd be much more
worried if it did not glow! Why do you think that this is a problem?
SQL Server writes modifications to disk in two ways. The actual data in
the tables is cached - changes are written to the internal memory
structures only; the changes are flushed to disk at regular intervals or
when the server is shut down. But in order to prevent data loss on power
outage and other disasters, all changes are also written (in a compact
format) to the transsaction log - and these writes are NOT cached (and
they should not be, because you'd otherwise still lost this data on a
power failure).
If your database processes changing data at millisecond intervals,
you'll be writing to the log file in millisecond intervals.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Can you please help me to find out what information should i collect
using the perform and profiler.
I collected the Avg Disk Queue length and the % processor time. the
Avg.Disk Queue length is always on top of the graph(100%). The %
processor time is also high around 95 in the graph. most of the time it
stays at 30 - 35.
I'm trying to figure out why this is happening. please bear with me.
i'm quite new to database
About my schema, actually the 9 tables are independent tables. and each
of them have a primary key. And the server application will keep
writing to these tables
An application is writing to the SQL Server database for every few
millisec. The disk I/O LED is continuously glowing when writing to the
database. Is there a problem with the configuration of the database ?.
How do i reduce the disk i/o when writing to the database. the
application is writing to the database using the 'insert' sql statement.Hi
Just a guess.
Check out that dusring the inserting you don't have page splits.
I think you have RAID5 disk configuration which is probably not the best
choice for SQL server because it imposes
a significant IO hit on write performance (both data and parity info must be
written)
<vanisathish@.gmail.com> wrote in message
news:1125888842.735920.102570@.z14g2000cwz.googlegroups.com...
> Hi All,
> An application is writing to the SQL Server database for every few
> millisec. The disk I/O LED is continuously glowing when writing to the
> database. Is there a problem with the configuration of the database ?.
> How do i reduce the disk i/o when writing to the database. the
> application is writing to the database using the 'insert' sql statement.
>|||I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
Harddisk. There are so many other threads running on the system. And
the application that writes to the sql database use the 'insert' sql
statement. do i need to use SQL stored procedure for this ?
Normally what is the approach that will be used to write to the sql
database from a VC++ application using ODBC|||Hi
SQL Server's perfomance will benefit from using stored procedures instead of
'insert' statements from the client
http://www.sql-server-performance.c..._procedures.asp
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Every transaction need to do physical I/O, otherwise, you couldn't trust the
data to be persisted
when you do commit. You can reduce the amount of I/O by combining several mo
difications in one
transaction, but you also need to consider transaction semantics as well as
blocking.
Make sure you don't have ODBC tracing turned on, btw...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||How much data are you writing? And how long does it take? Adding another
drive might help if you put the log on it, but running heavy operations on a
single processor, single disk, low memory system with lots of other stuff
running is not likely to run better. This can be made even worse if you
happen to have a very fragmented disk or (and this is the killer) you need
more than a GB of RAM and you have to do virtual memory paging.
As far as stored procedures, if you are doing many insert statements, a
stored procedure might help, but if it is just a single statement taking a
long time, then it is not likely.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<vanisathish@.gmail.com> wrote in message
news:1125898974.662403.62110@.g47g2000cwa.googlegroups.com...
>I am running sql-server on a desktop PC with 1GB of RAM and 60 GB
> Harddisk. There are so many other threads running on the system. And
> the application that writes to the sql database use the 'insert' sql
> statement. do i need to use SQL stored procedure for this ?
> Normally what is the approach that will be used to write to the sql
> database from a VC++ application using ODBC
>|||Actually i am not writing to much of data to the database. I will be
writing around 50 - 100 bytes of data to 9 tables. This writing happens
very often to some of the tables of the order of few millisec.
Currently i'm running a IIS web-server and some other application
servers which continuously polls some devices for every 250 msec
I see the harddisk LED glowing continuously, when the data logging is
ON ie. when writing to database happens.
As i said earlier the desktop PC is having 1GB of RAM and 60 GB
harddisk and is running win XP
I'm thinking of running the database server on a separate PC. Will that
solve my problem ?|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
>Actually i am not writing to much of data to the database. I will be
>writing around 50 - 100 bytes of data to 9 tables. This writing happens
>very often to some of the tables of the order of few millisec.
SQLServer will insist on doing at least TWO physical writes to the
disk for every transaction, one for the log, the other for the real
database.
>Currently i'm running a IIS web-server and some other application
>servers which continuously polls some devices for every 250 msec
>I see the harddisk LED glowing continuously, when the data logging is
>ON ie. when writing to database happens.
Try using perfmon to get more accurate data, the LED stays on long
enough for you to see, even if the disk is busy only for a fraction of
that time.
>As i said earlier the desktop PC is having 1GB of RAM and 60 GB
>harddisk and is running win XP
Best SQLServer performance requires putting the log on a separate
physical disk, perhaps you begin to see why!
>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
It may, but you should also consider getting a big, fat server-class
machine and not a desktop.
You might also consider writing some middleware that accumulates your
data for a couple of seconds at a time before writing it.
You haven't said anything about the schema of the tables you're
writing to, do you mean you always write to 9 tables? Do they have
PKs? What kind of PKs? Any other indexes? Any other DRI?
All that said, what the heck, let the little light stay on, that's
what it's there for!
But again, get some better numbers with perfmon and profiler.
J.|||On 5 Sep 2005 00:59:38 -0700, vanisathish@.gmail.com wrote:
(snip)
>I'm thinking of running the database server on a separate PC. Will that
>solve my problem ?
Hi vanisathish,
What exactly IS your problem? The only thing I've seen so far is that
your Disk I/O LED is glowing continuously - frankly, I'd be much more
worried if it did not glow! Why do you think that this is a problem?
SQL Server writes modifications to disk in two ways. The actual data in
the tables is cached - changes are written to the internal memory
structures only; the changes are flushed to disk at regular intervals or
when the server is shut down. But in order to prevent data loss on power
outage and other disasters, all changes are also written (in a compact
format) to the transsaction log - and these writes are NOT cached (and
they should not be, because you'd otherwise still lost this data on a
power failure).
If your database processes changing data at millisecond intervals,
you'll be writing to the log file in millisecond intervals.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Can you please help me to find out what information should i collect
using the perform and profiler.
I collected the Avg Disk Queue length and the % processor time. the
Avg.Disk Queue length is always on top of the graph(100%). The %
processor time is also high around 95 in the graph. most of the time it
stays at 30 - 35.
I'm trying to figure out why this is happening. please bear with me.
i'm quite new to database
About my schema, actually the 9 tables are independent tables. and each
of them have a primary key. And the server application will keep
writing to these tables
Subscribe to:
Posts (Atom)