Database

Aman Bhullar23-Jan-09 2:37

Aman Bhullar

23-Jan-09 2:37

My suggestion are as

1. Intel Xeon Quad Core2 (Two Processors i.e. 8 Cores)
2. RAM 4 GB
3. SATA 2 HDD
4. Windows 2003

We are able to handle nearly 500 Concurrent Users and CPU utilization is 10%(MAX).

Ennis Ray Lynch, Jr.23-Jan-09 3:07

Ennis Ray Lynch, Jr.

23-Jan-09 3:07

Remove OLAP services into another DB that is not on your OLTP db. Reporting cripples OLTP databases.

I can squeeze 100 users onto an ASP.NET/SQL Express Business Application using a shared VM with good design techniques so if your dedicated server is not fast enough consider looking at the system architecture. Good design scales a lot faster than hardware upgrades.

Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.

If you don't ask questions the answers won't stand in your way.

Most of this sig is for Google, not ego.

Wendelius23-Jan-09 3:29

23-Jan-09 3:29

Most likely the problem is in the overhead that OLAP services cause. The main target for OLAP services is reporting, not transaction handling. Instead of OLAP use the OLTP engine of SQL Server in other words the normal SQL Server engine and design the relational model so that it supports continuous data manipulation and retrieval.

The need to optimize rises from a bad design.My articles[^]

felopater23-Jan-09 9:46

felopater

23-Jan-09 9:46

Thanks alot for replies, so far i got that we should take alook at our design again, and try to seperate db engieen and OLAP to be in two servers instead of one, Smile | :)

Am i correct?

marcoryos

Wendelius23-Jan-09 9:48

Linked-Server SQL Server 2000

23-Jan-09 9:48

I think that's a good way to go. Smile | :)

The need to optimize rises from a bad design.My articles[^]

Indra PR22-Jan-09 20:57

Re: Linked-Server SQL Server 2000

22-Jan-09 20:57

Dear all, I have a linked-server on my database. My senior in my company make it like that, because in the future, we will have some servers to handle some applications in the company. But after we make it so and running it, the transfer is so damn slow. Is it because the linked-server? Are there any better ways to access database from multi-server in one application. Using mirror or something? If you have any reference for this, please help me. Thanks.

- No Signature Available -

Wendelius22-Jan-09 21:34

Re: Linked-Server SQL Server 2000

22-Jan-09 21:34

Indra PR wrote:
the transfer is so damn slow

Operations done over linked server may be performed very differently than those executed locally. You can use execution plan to investigate what's being done and why it's slow. So you should investigate each problem statement (especially multi database joins are often problematic).

The idea in optimizing multi server statements is that you transfer minimal amount of data using the linked server so in many cases the SQL may have to be rewritten.

Indra PR wrote:
Are there any better ways to access database from multi-server in one application

Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)

The need to optimize rises from a bad design.My articles[^]

Indra PR22-Jan-09 21:56

Re: Linked-Server SQL Server 2000

22-Jan-09 21:56

Mika Wendelius wrote:
especially multi database joins are often problematic

I've read something about this before, it is said that the performance will be better if we create a temporary table from the table in other database first. Then we join the temporary table with the 2nd table. Is that right?

Mika Wendelius wrote:
Linked server is intended for this. Of course there are other techniques such as replication etc, but the data is not typically up to date in those scenarios or you cannot update the data on the other end (stand-by solution etc)

If it so, then it is right for me to use linked-server, and I don't have any other choice of this, the only way is to optimize the query. Isn't it? Correct me if I'm wrong. Wink | ;)

- No Signature Available -

Wendelius22-Jan-09 22:17

Problems with SQL Server 2000 connection

22-Jan-09 22:17

Indra PR wrote:
I've read something about this before, it is said that the performance will be better if we create a temporary table from the table in other database first.

That can be part of the solution but not the solution itself. The main point is how much data you tranfer through the link. Fore example if you have a query like

select ...
from linkedservertable 
join localtable
where ...

SQL Server may have transfer the whole linked server table to this server before the join or row elimination can be done. If the same query can be written to format

select ...
from localtable
(possibly join to linkedservertavle)
where joiningcolumn in (select keycolumns
                        from linkedservertable
                        where restrictive conditions...)

the performance may be very different since the elimination may be done at the linked server. This is a very simple example, but hopefully points out the idea. Temporary tables can be used for that exact purpose if the operation cannot be re-written otherwise.

Indra PR wrote:
then it is right for me to use linked-server, and I don't have any other choice of this

Based on your description I would say that linked server is a good way to handle your situation.

One more thing. If you insert/update/delete your data on both servers in a same transaction you are forced to use distributed transactions. MS DTC takes care of this, but it's good to know that this will add extra overhead to transaction handling, which then again makes the operation a bit slower. However if you want the transaction to be ACID, that's the correct way to do it.

The need to optimize rises from a bad design.My articles[^]

Indra PR22-Jan-09 20:52

22-Jan-09 20:52

Dear all, I have some problems with SQL Server 2000 that I want to ask. I am using the database with OLEDB connection that is called from VB.NET. The application that I make is an ERP software.

1. Sometimes, and it is often happened in the application, is an error saying "Connection is busy".
2. Then sometimes the application also shows a dialog box with a connection error saying "Cannot create a new transaction becase capacity was exceeded".

I don't know how to solve them, I've tried to look for the answers from Google, but the only answer that I found is that I should update the service pack into the latest version. But I've checked my database, and its version is SP4. From the application itself, some people said that maybe I forgot to close the connection, but I've made only one function to fill a datatable, and I've put the Connection.Close() in the end after doing every transaction. Are there any better answer for these?

- No Signature Available -

Wendelius22-Jan-09 20:57

22-Jan-09 20:57

Check your connection pooling settings. You may be hitting the limit. Also check that you close the connections properly using Close method after you have used them and that you end the transactions gracefully.

The need to optimize rises from a bad design.My articles[^]

Indra PR22-Jan-09 21:06

22-Jan-09 21:06

I still don't know about the connection pooling Mika. Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time? Can you give me a little description about that, and what should I do to check it, or maybe a link of reference. Thanks before.

- No Signature Available -

Wendelius22-Jan-09 21:24

22-Jan-09 21:24

Indra PR wrote:
I still don't know about the connection pooling Mika

Connection pooling is quite well decsribed here: OLE DB, ODBC, and Oracle Connection Pooling [^].

Indra PR wrote:
Is it possible that the problem was caused by the fact that a lot of users of the application were accessing the database at the same time

If you have a lots of users simultaneously (thousands of them) or if you have limited maximum amount of connections, that's possible. You can check the max connection count using:

SELECT @@MAX_CONNECTIONS

You can find the maximum capacity specifications here: http://msdn.microsoft.com/en-us/library/aa933149.aspx[^]

The need to optimize rises from a bad design.My articles[^]

Aman Bhullar22-Jan-09 21:17

Aman Bhullar

22-Jan-09 21:17

Indra PR wrote:
I've put the Connection.Close()

although, you have put close connection request after every command. Does your program make sure that connection.close() is executed every time.

I mean to say, If there is a error during the transaction, is close() method called.

try using code as

try
{
   Connection.Open();
   //Your Block
}
catch(Exception EX)
{
   //Exception handling code
}
finally
{
    Connection.Close();
}

One thing you should try checking is
1. When Your application starts giving the error reported, at that instance of time how many users are connected to your server and on the particular DB.

2. How many requests from the application are running in pool of DB

Indra PR22-Jan-09 21:40

22-Jan-09 21:40

Yep, my code is exactly as the same as yours. I still don't know about pool? Before, Mika also said something about DB pooling, or pooling connection. What is that? I've read the reference but still don't understand Laugh | :laugh:

I only know that DB pooling is a feature in ADODB to minimize cost of opening connection, we have to set it first or what? Confused | :confused:

- No Signature Available -

Aman Bhullar23-Jan-09 2:27

Aman Bhullar

23-Jan-09 2:27

What is the Edition of your SQL Server

Indra PR23-Jan-09 19:30

actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

23-Jan-09 19:30

SQL Server 2000 Enterprise Edition

- No Signature Available -

anujanujanuj22-Jan-09 17:52

anujanujanuj

22-Jan-09 17:52

actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

Re: actually i have a database table have four fields rnkid, rnkartid,rnkuserid and rnk marks i want sum all the marks which have same rnk art id . pls hel tell how

Wendelius22-Jan-09 18:12

T-Sql programming in Sql server 2005

22-Jan-09 18:12

If I understood you correctly, your query could be something like:

select sum(rnkmarks)
from your table
group by rnkartid

See SUM[^] and GROUP BY[^] for more details.

The need to optimize rises from a bad design.My articles[^]

maheesh22-Jan-09 17:33

maheesh

22-Jan-09 17:33

Hi,
I'm new to T-Sql programming in Sql server with C++ Application.
Plz provides some examples.

With Regards
Mahesh

Re: T-Sql programming in Sql server 2005

Wendelius22-Jan-09 18:14

Transfer the data from xls sheet to sqlserver using SSIS

22-Jan-09 18:14

You could start for example here: Connecting to a Data Source (ADO.NET)[^]

The need to optimize rises from a bad design.My articles[^]

bruze22-Jan-09 3:20

bruze

22-Jan-09 3:20

Hi,

I need to transfer the data from xls sheet to sqlserver but actually the thing is if the source excel file has different sheets, in each sheet i have the data

and i need to move the entire data( all the data that is present in all sheets of the excel file) to a single table into sql server

like wise I have many xls files ( which have many sheets ) .

for eg:

excel file 1:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 2:

-> sheet 1

-> sheet 2

-> sheet 3

excel file 3:

-> sheet 1

-> sheet 2

-> sheet 3

now i need to get the data from all of the files and i need to insert into a single table ( sql server) in ssis package

so plz help me by giving the solution .

thanks

Re: Transfer the data from xls sheet to sqlserver using SSIS

Wendelius22-Jan-09 3:30