Showing posts with label total. Show all posts
Showing posts with label total. Show all posts

Monday, March 19, 2012

Explicit Transactions

I have an cursor that loops 720 times.
Each FETCH does 6 INSERTS.
Each FETCH the 6 INSERTs a total of 200-300 records appended.

Would explicit transactions speed up the cursor?

In Oracle, I would keep track of how many times I've looped, and commit every nth time. I would put a catch remainder COMMIT at the end of the procedure.

I've got the IF @.@.TRANCOUNT > 19 COMMIT TRAN (about 6k records), but say I had <= 19 remaining at the end of the cursor, how do you get those to commit? I tried to put an IF @.@.TRANCOUNT > 1 COMMIT TRAN, but that didn't work.

What's a good @.@.TRANCOUNT to commit at in this case?

Thanks crew,
CarlIs it possible to transform the cursor to a set based operation which may optimize the operation. can you post your code?|||Carl, Oracle is cursor heavy, and often requires their use. SQL Server is optimized for set-based operations, and cursors are usually the worst method of managing data.|||I was able to go set based with the sub procedure at the prompting of you folks. Perhaps I can get rid of this cursor as well. If I can

SELECT CASE x=y THEN INSERT INTO xyz (SELECT * from abc) END

Can you do stuff like this?|||INSERT INTO xyz
SELECT *
from abc
where x=y

...but you should enumerate your columns and avoid the use of "select *..."

Explain how to set the Total Mem Allocation using /3GB

/3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is it?
Can it be used to increase the Total memory allocation to a process? If so,
how is that process set?
Regards,
Jamie
the switch can be set in boot.ini on Windows 2K Adv.
Or do it using sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
Only if your Server has more than 2 GB of RAM
If you use the /PAE in Boot.ini and AWE in SQL 2000 Ent allows SQL
Server 2000 to utilize more than 4 GB memory
Without the /PAE switch SQL Server can only utilize up to 3 GB of
memory.
On Jan 22, 10:17 am, thejamie <theja...@.discussions.microsoft.com>
wrote:
> /3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is it?
> Can it be used to increase the Total memory allocation to a process? If so,
> how is that process set?
> --
> Regards,
> Jamie
|||/3GB is not used for SQL enterprise edition ONLY on Win2K Adv Server. You can
use it on 32-bit SQL2000/2005 enterprise edition with 32-bit Win2K/Win2K5
enterprise edition if you want the buffer pool of your SQL instance to use
more than 2GB of memory. (I only work with the enterprise editions of SQL
Server and Windows, so I can't comment on how it may behave on a
non-enterprise edition)
For some empirical data, see
http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx.
Linchi
"thejamie" wrote:

> /3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is it?
> Can it be used to increase the Total memory allocation to a process? If so,
> how is that process set?
> --
> Regards,
> Jamie
|||Since it is the Standard SQL Ed, I suspect the AWE does nothing and that the
/3GB already implemented will give me the biggest bang for the buck. THere
is actually 6 GB available on the Windows Advanced Server 2000. The extra
RAM has reduced the cpu load... instead of an 80% RAM usage it is now 31%
but 31% is still, I think, just 2 GB which is as it should be.
Regards,
Jamie
"Linchi Shea" wrote:
[vbcol=seagreen]
> /3GB is not used for SQL enterprise edition ONLY on Win2K Adv Server. You can
> use it on 32-bit SQL2000/2005 enterprise edition with 32-bit Win2K/Win2K5
> enterprise edition if you want the buffer pool of your SQL instance to use
> more than 2GB of memory. (I only work with the enterprise editions of SQL
> Server and Windows, so I can't comment on how it may behave on a
> non-enterprise edition)
> For some empirical data, see
> http://sqlblog.com/blogs/linchi_shea/archive/2007/01/11/awe-and-3gb-an-empirical-picture.aspx.
> Linchi
> "thejamie" wrote:

Explain how to set the Total Mem Allocation using /3GB

/3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is it
?
Can it be used to increase the Total memory allocation to a process? If so,
how is that process set?
--
Regards,
Jamiethe switch can be set in boot.ini on Windows 2K Adv.
Or do it using sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
Only if your Server has more than 2 GB of RAM
If you use the /PAE in Boot.ini and AWE in SQL 2000 Ent allows SQL
Server 2000 to utilize more than 4 GB memory
Without the /PAE switch SQL Server can only utilize up to 3 GB of
memory.
On Jan 22, 10:17 am, thejamie <theja...@.discussions.microsoft.com>
wrote:
> /3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is
it?
> Can it be used to increase the Total memory allocation to a process? If
so,
> how is that process set?
> --
> Regards,
> Jamie|||/3GB is not used for SQL enterprise edition ONLY on Win2K Adv Server. You ca
n
use it on 32-bit SQL2000/2005 enterprise edition with 32-bit Win2K/Win2K5
enterprise edition if you want the buffer pool of your SQL instance to use
more than 2GB of memory. (I only work with the enterprise editions of SQL
Server and Windows, so I can't comment on how it may behave on a
non-enterprise edition)
For some empirical data, see
http://sqlblog.com/blogs/linchi_she...>
icture.aspx.
Linchi
"thejamie" wrote:

> /3GB switch is used for SQL Enterprise Ed only on Win2k Adv Server, or is
it?
> Can it be used to increase the Total memory allocation to a process? If
so,
> how is that process set?
> --
> Regards,
> Jamie|||Since it is the Standard SQL Ed, I suspect the AWE does nothing and that the
/3GB already implemented will give me the biggest bang for the buck. THere
is actually 6 GB available on the Windows Advanced Server 2000. The extra
RAM has reduced the cpu load... instead of an 80% RAM usage it is now 31%
but 31% is still, I think, just 2 GB which is as it should be.
--
Regards,
Jamie
"Linchi Shea" wrote:
[vbcol=seagreen]
> /3GB is not used for SQL enterprise edition ONLY on Win2K Adv Server. You
can
> use it on 32-bit SQL2000/2005 enterprise edition with 32-bit Win2K/Win2K5
> enterprise edition if you want the buffer pool of your SQL instance to use
> more than 2GB of memory. (I only work with the enterprise editions of SQL
> Server and Windows, so I can't comment on how it may behave on a
> non-enterprise edition)
> For some empirical data, see
> http://sqlblog.com/blogs/linchi_she...
-picture.aspx.
> Linchi
> "thejamie" wrote:
>

Monday, March 12, 2012

Expert needed for this one( Select with sums on self joined table)

I have a services table that holds two types of records(new & old) and among the fields I have is Price. I need to add the total Price of all the new records and all total price of the old records. so I came up with this code.

SELECT Sum(tbl1.Price) AS OldCost, tbl1.ID, Sum(tbl2.Price) AS NewCost, tbl2.ID
FROM tblServices AS tbl1 INNER JOIN tblServices AS tbl2 ON tbl1.ClientID = tbl2.ClientID
WHERE ((tbl1.Type)="O") AND ((tbl2.Type)="N"))
GROUP BY tbl1.ClientID, tbl2.ClientID;

Now this almost works. except for the fact that it is doing some weird stuff by multiplying the totals times the number of records that it is pulling or something really close to that. I have never seen this before.

As a end Result I want three fields per record.
Total of New Services, Total of Old Services, and The client ID.

I have looked at it for 3 hrs and tryed everything I know to try. If I separate the query into two separate ones ( 1 for new and one for Old) it works perfectly but combinding them it just craps out.

Any Advice on what I am doing wrong.?
I really need some help...I am using access for my front end and I was playing around for kicks and I you can take two querys and make a nother one base doff those two, so that was my awnser.
Thanks

Wednesday, March 7, 2012

Expand/collapse matrix cells

Hi folks - I am trying to figure out how to set up the visibility
(expand/collapse) for my grouped matrix. So for example:
A
A1 10
A2 15
Total A 25
B
B1 35
B2 30
Total B 70
I want the user to be able to click and collapse to just see this:
A 25
B 70
Is this possible? I get the table, but am confused on how the matrix
handles grouping and visibility...
-RyanIn a matrix, you would have two row groupings. One for A, B, etc. and an
inner grouping for A1, A2, B1, etc.
1. For the inner row grouping, edit the grouping expression and go to the
Visibility tab of the grouping and sorting dialog. Set initial visibility to
"Hidden". Then, select "Visibility can be toggled by another reportitem" and
select the textbox of the outer row grouping heading.
2. For the inner row grouping, go to its heading textbox properties and set
the "initial appearance of the toggle" to Collapsed.
I also recommend to look at the sample reports that come with RS 2000 / RS
2005. Specifically, check the "Company Sales" sample report - it should
provide what you are looking for.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Ryan M" <ryan.marples@.bluetidemanagement.com> wrote in message
news:1128026677.096400.11570@.f14g2000cwb.googlegroups.com...
> Hi folks - I am trying to figure out how to set up the visibility
> (expand/collapse) for my grouped matrix. So for example:
> A
> A1 10
> A2 15
> Total A 25
> B
> B1 35
> B2 30
> Total B 70
> I want the user to be able to click and collapse to just see this:
> A 25
> B 70
> Is this possible? I get the table, but am confused on how the matrix
> handles grouping and visibility...
> -Ryan
>|||Thanks for the pointer Robert. It turned out what I was doing wrong is
... applying the visibility properties to the textbox that represented
the group, not the group itself. Now I get it, thanks!
Now if I could just figure out how to get column heading for my row
groups...
-Ryan