|
Navin C. Pandit wrote: Actually I want to know the max. number of possible concurrent users, beyond
that number database may not listen about the user/request.
You are mixing terms.
User != connections.
A connection represents a TCP connection to the database.
A "user", say for example a real person running a GUI can have 1 or more connections to a database.
|
|
|
|
|
Many thanks for your nice reply Jschell!
Sorry, unfortunately I wrote the term connection. My focus is about to max. possible number of database concurrent user only.
Thanks,
|
|
|
|
|
If it was me I would investigate the requirement itself.
But other than that presuming it is possible you might to research it using the term "session" or "user session".
|
|
|
|
|
In most application I have seen code that searches records using a number of optional fields.
I have to build such a screen with a table that is expected to contains several million records.
Here a short example that exposes my questions, I would like to here your comments on it:
CREATE PROCEDURE SearchContacts
(
@Reference varchar(8) = NULL,
@DateOfBirth datetime = NULL,
@ContactName varchar(50) = NULL,
@ContactType varchar(10) = NULL
)
AS
BEGIN
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBirth IS NULL OR Contacts.DateOfBirth = @DateOfBirth)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
END
This query is very simple however it is probably not going to take advantage of the indexes that are available. As I have to deal with a table that will contain a lot of record, this won't be acceptable.
An alternative would be to hard code many if :
IF (@ContactName IS NULL AND @ContactType IS NULL AND @Reference IS NOT NULL)
BEGIN
SELECT * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBirth IS NULL OR Contacts.DateOfBirth = @DateOfBirth)
END
ELSE IF (@Reference IS NULL AND @DateOfBirth IS NULL)
BEGIN
SELECT * FROM Contacts WHERE
(@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
END
...
This can probably use an index but it is very tedious to write in this example the 16 combination of parameters that can happen.
The next step is to try with a dynamic query
SET @Sql = 'SELECT TOP 100 * FROM Contacts WHERE TRUE '
IF @Reference IS NOT NULL
BEGIN
SET @Sql = @Sql + ' AND Contacts.Reference = ''' + QUOTENAME(@Reference,'') + ''''
END
IF @DateOfBirth IS NOT NULL
BEGIN
SET @Sql = @Sql + ' AND Contacts.DateOfBirth = ''' + QUOTENAME(@DateOfBirth,'') + ''''
END
...
On this last sample I assume this will allow the optimizer to use the right index.
But will the optimization would have to be done on every request?
Is there a more elegant way to write this sort of queries while keeping maximum efficiency ?
modified on Tuesday, August 16, 2011 11:34 AM
|
|
|
|
|
It looks as if using a stored procedure is the limiting factor.
I'd just cobble up the SQL in code and execute it. Slick as snot.
Something along the lines of (not tested, and I'm not awake yet either):
List<string:gt; parts = new List<string>() ;
List<IDbDataParameter> parms = new List<IDbDataParameter>() ;
cmd.CommandText = "SELECT TOP 100 * FROM Contacts " ;
if ( !System.String.IsNullOrEmpty ( Reference ) )
{
parts.Add ( "Contacts.Reference = @Reference " ) ;
IDbDataParameter prm = cmd.CreateParameter() ;
prm.Name = "@Reference" ;
prm.Value = Reference ;
parms.Add ( prm ) ;
}
...
if ( parts.Count > 0 )
{
cmd.CommandText += "WHERE " ;
cmd.CommandText += parts [ 0 ] ;
cmd.Parameters.Add ( parms [ 0 ] ) ;
for ( int i = 1 ; i < parts.Count ; i++ )
{
cmd.CommandText += "AND " ;
cmd.CommandText += parts [ i ] ;
cmd.Parameters.Add ( parms [ i ] ) ;
}
}
cmd.ExecuteReader() ;
|
|
|
|
|
In C# you're right it is easier, you won't have the sql injection problem.
Nice piece of code you got my 5.
A collegue gave me a good solution in T-SQL. I'll be posting it too.
|
|
|
|
|
The answer above is valid in C#.
My good collegue and friend James H gave me some hints on how to fix it in T-SQL.
DECLARE @ContactName NVARCHAR(255)
DECLARE @Reference NVARCHAR(255)
DECLARE @sql NVARCHAR(255)
SET @sql = 'SELECT TOP 100 FamilyName,Reference FROM Contact C WHERE 1=1'
IF NOT @ContactName IS NULL
SET @sql = @sql + ' AND C.ContactName = @ContactName'
IF NOT @Reference IS NULL
SET @sql = @sql + ' AND C.Reference LIKE @Reference'
EXECUTE sp_executesql
@sql,
N'@FamilyName NVARCHAR(255), @CoName NVARCHAR(255)',
@ContactName, @Reference
This approach is much better for several reasons:
- no quoted parameters, the parameters are passed by name so there is no possible SQL injection
- because the query do not change on each request we can assume it will be optimized and cached
|
|
|
|
|
Pascal Ganaye wrote: we can assume it will be optimized and cached
As I understand it, in SQL Server every statement is cached, and may get pushed out of the cache. I assume that this way will also cause the dynamic SQL to be cached separately from the main SQL. So I don't think there's any appreciable savings over the non-stored-procedure way.
|
|
|
|
|
PIEBALDconsult wrote: So I don't think there's any appreciable savings over the non-stored-procedure way
This really hurts but that is my understanding as well.
This one of the areas where sql strings from the business layer are actually easier to do!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: sql strings from the business layer are actually easier to do
I think that's true all the time. Using stored procedures requires that you write the stored procedure and still you have to write some SQL (an EXEC statement) in the code and add the appropriate parameters, and error handling, etc. Keeping it all in code simplifies things -- everything in one place, the SQL won't change or disappear mysteriously.
|
|
|
|
|
We've already had this conversation!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Others may have missed it.
|
|
|
|
|
PIEBALDconsult wrote:
everything in one place, the SQL won't change or disappear mysteriously.
You have some odd database issues if your procs are disappearing
Common sense is admitting there is cause and effect and that you can exert some control over what you understand.
|
|
|
|
|
Just one, on SQL Server 6 on WinNT, about ten years ago -- it simply disappeared occasionally. I see no reason to trust them when there are better solutions now.
|
|
|
|
|
Just curious to know, what makes you think that SQL Server will not use indexes for this query?
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
|
|
|
|
|
I am trying to stay database agnostic on this, I might be wrong on some databases.
When you write the query below in a stored procedure, the database engine will typically build an execution plan for it.
SELECT TOP 100 * FROM Contacts WHERE
(@Reference IS NULL OR Contacts.Reference = @Reference)
AND (@DateOfBith IS NULL OR Contacts.DateOfBith = @DateOfBith)
AND (@ContactName IS NULL OR Contacts.ContactName = @ContactName)
AND (@ContactType IS NULL OR Contacts.ContactType = @ContactType)
At that time it will still have to decide which index to use.
Whatever choice it makes at compile time is doomed to be wrong.
The optimizer can't choose the right index, because there is not right index.
It would have to choose the index that is right with a set of given parameters.
Even if it were to delay the choice till the first use of the procedure, then the second use might be done with different parameters and another index should be used.
From what I can see in MS SQL2008, the execution plan is invariably a Clustered Index Scan.
Index Scan meaning that it will read the entire index, from A to Z. This can be terribly slow on a very large table.
If the query is dynamic however then the query becomes a lot simpler.
SELECT TOP 100 * FROM Contacts WHERE
(Reference = @Reference)
AND (DateOfBith = @DateOfBith)
The 'OR' disappear. The execution plan then is a lot better. Typically it will use an Index Seek which is only a few disk access and not a full read.
If you use SQL Server Management Studio you can check that using the 'Include Actual Execution Plan' button.
|
|
|
|
|
Pascal Ganaye wrote: I am trying to stay database agnostic
Then don't use stored procedures; not all databases support them.
(@ContactType IS NULL OR Contacts.ContactType = @ContactType)
How about
Contacts.ContactType = COALESCE(@ContactType,Contacts.ContactType)
(Not that that is necessarily agnostic either. )
|
|
|
|
|
What is the best precise method of converting bit(0 or 1) from sql(row array text cell) to boolean value.
example: bool? b = (bool?)(row.cells[0].Text)
Thanks. If possible one line of code please
I only read newbie introductory dummy books.
modified on Tuesday, August 16, 2011 7:25 AM
|
|
|
|
|
SELECT FORUM[^] FROM CODEPROJECT WHERE FORUM = "DATABASE"
Panic, Chaos, Destruction. My work here is done.
Drink. Get drunk. Fall over - P O'H
OK, I will win to day or my name isn't Ethel Crudacre! - DD Ethel Crudacre
I cannot live by bread alone. Bacon and ketchup are needed as well. - Trollslayer
Have a bit more patience with newbies. Of course some of them act dumb - they're often *students*, for heaven's sake - Terry Pratchett
|
|
|
|
|
Your database structure is not normalized
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
5fingers wrote: What is the best precise method of converting bit(0 or 1) from sql(row array text cell) to boolean value.
Probably by asking it in the correct forum and by reading the message at the top of the page that says...
Please do not post programming questions here.
------------------------------------
I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave
CCC Link[ ^]
Trolls[ ^]
|
|
|
|
|
1. create table test(name varchar(20),status bit not null)
assume status has value 0 or 1. then fill to a dataset.
Bind a gridview directly to dataset.
Column status shows true or false on gridview which is ok.
To retrieve the status value from gridviewrow.cells[1].text return empty string.
Confirm.
I only read newbie introductory dummy books.
|
|
|
|
|
See here[^]
Every man can tell how many goats or sheep he possesses, but not how many friends.
|
|
|
|
|
See here[^].
If the Lord God Almighty had consulted me before embarking upon the Creation, I would have recommended something simpler.
-- Alfonso the Wise, 13th Century King of Castile.
This is going on my arrogant assumptions. You may have a superb reason why I'm completely wrong.
-- Iain Clarke
[My articles]
|
|
|
|