|
What error message are you getting when you try to rebuild the master?
|
|
|
|
|
Hey,
Here is the link to the log with failure : [log]
Can not make sense of it.
Thanks in advance.
|
|
|
|
|
Whenever we create either permanent table or temporary tables it requires modification of the database schema.
Is the same applicable for Collection objects?
ie when we create collection objects does it modify the database schema/metatdata?
|
|
|
|
|
This depends on the situation. For example if a collection is used in a procedure, information about the collection is stored in the system tables.
Could you explain the situation a bit more, what kind of collection are you thinking of and what usage?
|
|
|
|
|
Hi,
I am using a SSRS report and the report is generated by executing a stored procedure.
The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values.
I am executing a complex SQL statement.
For this i am using nested loop:
A as INPUT
B as INPUT
C as INPUT (String input seperated by ,)
D as input (String input seperated by ,)
Cursor Cur as OUTPUT
Loop for c
Loop for d
.
.
execute the above SQL Statement(select query)
.
.
END loop
END Loop
problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed to use permanent/temporary tables as they require definition at schema level and modificaton of schema is not allowed.
I need to save the value in the cursor at each runs, but in that case the previous results get overwritten.
So i wanted to know collection objects and wanted to know if that too requires definition at schema level.
|
|
|
|
|
Ok, well basically when you create a procedure (of any kind) and store it in the database, you modify the schema. So in this sense the requirement of leaving the schema intact doesn't make sense...
Anyhow, you can use collections and for example if you're using a package, you can define the collection in your procedure or at package level. When you loop through the cursor you can store the results in the collection and later on modify them or add more results etc. If you're using package level variables, you can store the results in them even between procedure calls.
|
|
|
|
|
when i store the results in the collection while i run the loop. can i save the results into the cursor.
|
|
|
|
|
Not directly. If you're using output cursors, you pass the cursor to the caller at the end but the cursor is based on a query from an object.
I know that you can create a cursor based on a nested table but I have never tried that with associative arrays or varrays.
|
|
|
|
|
Hi All,
Can anybody plz. answer what is the maximum number of concurrent user in PostGre database (with current version or any previous)?
Your reply is highly awaiting.
Thanks in advance!
With Regards,
Navin Pandit
Mail:navin2k6@gmail.com
Gurgaon, (India)
|
|
|
|
|
|
Many thanks Mika for your quick reply, but this determines the max. number of connection currently established with the server.
Actually I want to know the max. number of possible concurrent users, beyond that number database may not listen about the user/request.
I am not sure but in some forum it was mentioned around 12000. Is there any such number of possible users?
Regards,
Navin
|
|
|
|
|
Sorry, but I don't quite follow. From the settings documentation:
"Determines the maximum number of concurrent connections to the database server...This parameter can only be set at server start"
So my understanding is that with this startup parameter you define the maximum number of concurrent connections that can be taken to the server (well actually max_connections-superuser_reserved_connections). As far as I know, the actual maximum you can define is operating system dependent.
The setting itself is defined in postgresql.conf.
|
|
|
|
|
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.
|
|
|
|