Database

Re: Per-user connection string to get good perf, good idea?

16-Feb-06 12:52

The pro is that the view will return only the user's data because, I'm guessing, that it will be defined as something like this:

CREATE VIEW SELECT * FROM MyTable WHERE User = USER();

However, I don't see where the performance gain is on this because it will still be doing the same as before (it's just hidden).

Passing the user name and password in the connection string means that your application will lose some of its ability to use connection pooling. (If this is a thick client windows application with only one user at a time using it then this isn't really a problem, however if you have a web application with many users accessing it then losing the ability to effectively use connection pooling could introduce performance problems - However, I've never done any testing on that so I don't know. I'm flagging it as it may be a concern and something to look in to).

From your description of the problem I'd say that you have a problem with indexes rather than anything else. It is also the easiest solution because it won't require views to be created or code to be changed to have dynamic connection strings and so on.

Look at what columns are being referenced most often in the where clause (and it sounds like it will be something like the UserId column) and index it. If you are using SQL Server 2000 there is an index tuning wizard you may want to look at.

Does this help?

ColinMackay.net
Scottish Developers are looking for speakers for user group sessions over the next few months. Do you want to know more?

Judah Gabriel Himango16-Feb-06 15:45

Judah Gabriel Himango

16-Feb-06 15:45

Yeah I think it does help. I believe the performance gain is by the fact the view is created based on the current SQL user, something about how that works.

For indexing, there are certain tables we cannot index due to the table have TEXT fields. I understand the SQL 2005 now has varchar(MAX) which is index-able and can store something like 2 billions characters... we will have to look into that more though.

Thanks Colin.

Tech, life, family, faith: Give me a visit.
I'm currently blogging about: Connor's Christmas Spectacular!
Judah Himango

Partitioning

Edbert P16-Feb-06 16:20

Edbert P

16-Feb-06 16:20

I doubt creating a per-user connection string will be of much help.
You might want to have a look at SQL 2005 partitioning[^] instead.
From what I have learned/been told (I haven't tested this myself, but there's a performance statistic on the article) partitioning segments your data for better performance - you can even store the data on different hard disks.

Edbert
Sydney, Australia

Re: Partitioning

Judah Gabriel Himango16-Feb-06 16:44

Judah Gabriel Himango

16-Feb-06 16:44

According to our DB guy, it isn't that the per-user connection itself gives good perf, but rather, it allows you to create indexed views showing data only for that user...

Thanks for the paritioning pointer, I'll have a look at that.

T- SQL Inner Select

Greeky16-Feb-06 1:21

Greeky

16-Feb-06 1:21

There is two tables HP_AccountName and T_Values. HP_AccountName includes all main accounts,T_Values includes all operations about accounts. Sample Tables r fallowing.

HP_AccountName............................|T_Values (An Account can be writen different lines. )
------------------------------------------|-----------------------------
HP_Level..HP_No..HP_AccountName...........|....MFD_AccountNo..MFD_Total
1.........1......Main1Account.............|....1200254........10,000
2.........10.....Main1SubAccount1.........|....1002865........15,000
3.........100....SafeBoxes................|....1009431........20,000
2.........12.....Main1SubAccount2.........|....2005454.........0,500
3.........120....Customers................|....2000024.........5,000
1.........2......Main2Account.............|....1205471........35,000
2.........20.....Main2SubAccount1.........|....1205471.........0,600
3.........200....Renders..................|....1205471.........0,400
...............................................2006300........48,500

All I want is a result Set Like
HP_No..HP_AccountName.........TOTAL
1......Main1Account...........81,000
10.......Main1SubAccount1.....35,000
100........SafeBoxes..........35,000
20.......Main1SubAccount2.....46,000
120........Customers..........46,000
2......Main2Account...........54,000
20.......Main2SubAccount1.....54,000
200........Renders............54,000

I worte something like that but i couldnt do axactly what i want
Select SPACE(HP_Level*3)+ HP_AccountName,
(Select SUM(MFD_Total) FROM T_VALUES WHERE MFD_AccountNo LIKE HP_No + '%') FROM T_ACCOUNTS
WHERE HP_Level<=1 order by HP_No

-- modified at 7:22 Thursday 16th February, 2006

Re: T- SQL Inner Select

Colin Angus Mackay16-Feb-06 2:16

16-Feb-06 2:16

You might want to put your table examples inside <pre></pre> blocks. That way it will be readable.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

UML modeling

Klempie15-Feb-06 23:57

Klempie

15-Feb-06 23:57

Hi.

Does anyone know of a good freeware tool to simply draw UML diagrams for relational databases?

TIA

Re: UML modeling

Juan Pedro Prez16-Feb-06 2:05

Help with ADO.NET, please

16-Feb-06 2:05

Hi,

try on Google Poseidon for UML. Although I'm not an UML expert, I use this application and it looks great. It has a free Community Edition.

Best regards,

Juan Pedro Pérez

Re: UML modeling

Paul Conrad16-Feb-06 17:14

Paul Conrad

16-Feb-06 17:14

Juan Pedro Pérez wrote:
I use this application and it looks great

I agree with Juan. I've been using the Community Edition for the past few years during my graduate studies and it works very nicely. You may also want to check out Argouml even though it is Java based.

Hope this helps,

Paul

Re: UML modeling

Klempie16-Feb-06 19:25

Klempie

16-Feb-06 19:25

Thanks guys.

Having sniffed around I also found VisioModeler at Microsoft which is free as an unsupported product. First impressions look promising.

Juan Pedro Prez15-Feb-06 23:20

15-Feb-06 23:20

Hello,

I'm new to ADO.NET and I'm working on my first little application. I feel I'm missing something really important about this architecture, and I'm messing things up.

Lets say I have a database in Access with a Table A. This Table A have an autonumerical PK, so I don't have to worry about it. Up to this point, great. I managed to configure my DataAdapter with commands so I don't have to worry about the PK, for Access does this work for me.

Next, I create a Table B that have as a FK the aforementioned Table A PK. I duplicate in a DataSet this two tables, create a new row in A and then intended to create related rows in B to this new A row. And here is where all the hell broke loose.

As a single-user non-concurrent app, I decided to control the A PK directy in it. During the dataset construction, I read the highest PK from the database and create in DataSet-Table A a PK field with the autonumerical True and its seed fixed to that maximun readed from the DB. Then I generate every new DataSet Table A rows with a controlled PK, so I'm able to control its value to generate related rows in DataSet Table B. Although I suspect is a clumsy solution, by now it works.

But the question readily arrises. What if my app should be multiuser? How the hell can I "predict" new values in autonumerical PK controlled by the DB so I can relate rows to other tables off-line in my dataset and then merge my transactions with that of other users without PK conflicts?

Please, drop me a line. I'm suspecting I miss something fundamental and any indication could change all my messy mental schemes about ADO.NET.

Thanks in advance and greetings from Spain,

Juan Pedro Pérez

Mike Ellison16-Feb-06 8:04

Mike Ellison

16-Feb-06 8:04

Hi Juan. I wish my Spanish were as good as your English.

My initial question is why are you manipulating rows in an in-memory dataset, rather than just INSERTing a row in the database in TableA, then INSERTing related rows in TableB? Upon inserting the row in Table A, you can retrieve the newly created AutoNumber, by issuing a SELECT @@Identity.

Here's a (very raw, but complete) example of what I mean (in C#):

<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Page Language="C#" %>

<script runat="server">
  void btnSubmit_Click(object o, EventArgs e)
  {
    // provide a connection string to the database
    const string kCONNECT_STRING = 
      "Provider=Microsoft.Jet.OLEDB.4.0; "
      + @"Data Source=c:\inetpub\wwwroot\tests\test.mdb";
    
    // provide the insert statement, with @parameters
    const string kINSERT_STATEMENT = 
      "INSERT INTO [MyTable] ([E-Name], [E-Email]) " 
      + " Values (@pName , @pEmail) ";
    
    OleDbConnection con = null;
    OleDbCommand cmd = null;  
    OleDbParameter pName = null; 
    OleDbParameter pEmail = null; 
    int insertedID = -1;
    
    try
    {
      // get the connection
      con = new OleDbConnection(kCONNECT_STRING);
      con.Open();
      
      // create the insert command
      cmd = new OleDbCommand(kINSERT_STATEMENT, con);
      
      // add parameter values
      pName = new OleDbParameter("@pName", DbType.String);
      pName.Value = tbName.Text;
      cmd.Parameters.Add(pName);
      
      pEmail = new OleDbParameter("@pEmail", DbType.String);
      pEmail.Value = tbEmail.Text;
      cmd.Parameters.Add(pEmail);
      
      // execute the insert command
      cmd.ExecuteNonQuery();
      
      // retrieve the autonumber that was just inserted
      cmd.CommandText = "SELECT @@Identity";
      cmd.CommandType = CommandType.Text;
      cmd.Parameters.Clear();
      insertedID = (int)cmd.ExecuteScalar();

      
      // if we're still here there's been no exceptions;
      // display a message
      Response.Write(
        string.Format("Record has been inserted and assigned ID# {0}."
                      ,insertedID) );
      
    }
    catch (Exception x)
    {
      // on an error, display a message
      Response.Write("There has been an error.  ");
      Response.Write(x.Message);
    }
    finally
    {
      // dispose objects
      if (cmd != null) cmd.Dispose();
      if (con != null) con.Dispose();      
    }
      
  }    
</script>

<html>
  <head>
  </head>
  
  <body>
    <form runat="server">
      <h3>Insert into Access</h3>
      <table>
        <tr>
            <td>Name</td>
            <td><asp:TextBox id="tbName" runat="server" /></td>
        </tr>
        <tr>
            <td>Email</td>
            <td><asp:TextBox id="tbEmail" runat="server" /></td>
        </tr>
      </table>
      <asp:Button id="btnSubmit" runat="server" text="Insert"
                  onclick="btnSubmit_Click" />
    </form>
  </body>
  
</html>

Juan Pedro Prez17-Feb-06 6:06

17-Feb-06 6:06

I see. I think I was trying to do things "too off-line". I read from the very start that behind ADO.NET lies a disconnected philosofy, but it is clear that some tasks are very difficult to accomplish that way. I was trying to do all the changes off-line, and then run a bunch of DataAdapter.Update() to commit all the changes. In this case, when tables relies on PK of other tables, then the INSERTs should be done at once to obtain a solid PK, isn't it?

Thanks for your help,

Juan Pedro Pérez

Mike Ellison17-Feb-06 6:32

Mike Ellison

17-Feb-06 6:32

I would say that ADO.NET supports a disconnected DataSet - disconnected in that it is not specifically tied to any brand of relational database (and in fact can be created entirely in memory if desired).

But ADO.NET does support a consistent model of connecting to any brand of relational database (that has an ADO.NET provider) to either query for data, or modify data.

A common operation for example, is to use connection-specific objects to issue a SELECT statement to a specific database. For SQL Server, these objects would be SqlConnection, SqlCommand, and SqlDataAdapter. For an Access file, these objects would be OleDbConnection, OleDbCommand, and OleDbDataAdapter. The DataAdapter object is then used to .Fill a disconnected DataSet with the SELECT results. The disconnected DataSet may then be used throughout your application as necessary.

This is particularly useful when designing N-tier applications, where you want to separate database-specific code (into a Data tier) from your presentation code (.aspx pages). For example, your Data tier (think: custom class) could have a method that encapsulates the code necessary to connect to an Access database and query for a list of employees. This method could return this data as a DataSet object. The presentation code (your .aspx page) could then call this method and databind the resulting DataSet object to, for example, a GridView.

Now imagine that you want to replace your backend database, upgrading from Access to SQL Server. You would need to change the database-specific code in your Data tier, but you would not have to change your presentation layer at all. As long as your method for retrieving employees from SQL Server returns the disconnected DataSet, which is what your presentation layer expects, you don't need to make changes to the presentation code.

This is certainly an over-simplification of a discussion on N-tier architecture and Data tiers, but hopefully it gives you a sense of why the disconnected DataSet object in ADO.NET is useful (but also why you'll still use Database-specific code in ADO.NET too).

Juan Pedro Prez17-Feb-06 7:46

How to get record count from OledbDataReader?

17-Feb-06 7:46

Got it. Thanks for your clear explanation. The deployment in N-Tier you described is what I was trying to implement, with little success Smile | :)

I find the learning curve of ADO.NET a lot stepped as conventional ADO, but I know it's worth the time. A lot of more hours of programming and everything will be OK.

Greetings,

Juan Pedro Pérez

rushing15-Feb-06 23:00

rushing

15-Feb-06 23:00

I have get some records by OleDbDataReader like this,

OledbDataReader reader=cmd.ExecuteReader();

How to get the amount of records from it?
Thanks!

Re: How to get record count from OledbDataReader?

Colin Angus Mackay16-Feb-06 2:11

Re: How to get record count from OledbDataReader?

16-Feb-06 2:11

rushing wrote:
How to get the amount of records from it?

Read all the records from it counting them as you go. There is no other way with a DataReader.

But, you do have the option of of getting the database to tell you in advance (assuming you need to know in advance) by sending off a query to find out how many rows it expects to return.

You can do this in the same command as your main data, or you can use a separate command.

If it is the former you can do something like this:

OleDbCommand cmd = new SqlCommand();
cmd.Connection = myConnection;
cmd.CommandText = "SELECT COUNT(*) FROM MyTable; SELECT * FROM MyTable";
OleDbDataReader reader = cmd.ExecuteReader();
reader.Read(); // Reads the next row
int numRows = reader.GetInt();
reader.NextResult(); // Moves to the next result set in the reader
while(reader.Read()) // Reads the next row, returns false if there isn't another row
{
    // Do stuff.
}

Or you can use a separate command object requesting the count of the rows and use ExecuteScalar() on it.

Does this help?

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

rushing16-Feb-06 17:04

rushing

16-Feb-06 17:04

Thanks.
However, this sql statement does not work in Oracle.
It is seem that we could not write two sql statement in one line.

Adventure Works Database

Brendan Vogt15-Feb-06 22:56

Brendan Vogt

15-Feb-06 22:56

Hi All!!

I am working through the documentation online of the Adventure Works database for SQL Server 2005.

Is there any .NET 2.0 application that I can download that works well with this database?

And secondly, is see they name there tables like Person.Address, Person.AddressType, Person.Contact... etc. What does this mean? What is this feature called so that I can do some more reading on this topic.

Regards,
Brendan

Re: Adventure Works Database

Colin Angus Mackay16-Feb-06 2:24