Re: How can you tell if a SQL Server instance max'ed out bottleneck is Memory? - Database Discussion Boards

Re: Change cursor to common table

Richard Deeming15-Dec-14 2:56

Richard Deeming

15-Dec-14 2:56

You seem to be missing the definition of your GetAccount_ID cursor.

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Change cursor to common table

Amr Mohammad15-Dec-14 8:22

Amr Mohammad

15-Dec-14 8:22

I removed some parts of the function when posting may be I forgot and remove the declaration for

GetAccount_ID

and here is the declaration

SQL

DECLARE GetAccount_ID  CURSOR STATIC FOR
SELECT Account_ID FROM AccountTree WHERE Account_ParentID = @AccountID AND Account_Isleaf = 0

Re: Change cursor to common table

Richard Deeming15-Dec-14 10:32

Richard Deeming

15-Dec-14 10:32

It's tricky to answer without your table definitions and some sample data, but this should get you close:

SQL

WITH cteAccountTree As
(
    SELECT
        @AccountID As Account_ID

    UNION ALL

    SELECT
        P.Account_ID
    FROM
        cteAccountTree As P
        INNER JOIN AccountTree As C
        ON C.Account_ParentID = P.Account_ID
    And
        Account_Isleaf = 0
)
SELECT
    @TotalValue = IsNull(Sum(JournalDet_Debit), 0) * CASE WHEN @Currency = 0 THEN AccountBranch_CurrencyConv ELSE 1 END
FROM
    cteAccountTree As T
    INNER JOIN Account_InBranch As B ON B.AccountBranch_AccountID = T.Account_ID
    LEFT JOIN Journal_Details As JD ON JD.JournalDet_AccountID = T.Account_ID
    LEFT JOIN Journal_Head As JH ON JH.Journal_ID = JD.JournalDet_HeadID
WHERE
    Journal_BranchID = @Branch
And
    Journal_Date Between @DateFrom And @DateTo
;

The first part is a recursive common table expression (CTE):
How to use recursive CTE calls in T-SQL[^]

This should return the list of all accounts in the tree which have the specified account ID as an ancestor, excluding any with the Account_Isleaf flag set.

NB: If your tree is particularly deep, you might run into the default recursion limit. There will probably be a way to work around it, but it won't be as nice as the recursive CTE solution. Smile | :)

You then join the tree of account IDs to your branch and journal tables to calculate the total in one hit.

The only part I'm not sure about: your code seems to be double-counting at each level:

sql>SELECT

<hr size="1" color="#63B4FF" noshade="">
  <span style="color: rgba(0, 88, 170, 1)">
    <cite>"These people looked deep within my soul and assigned me a number based on the order in which I joined."</cite>
     - Homer
  </span>

Re: Change cursor to common table

Amr Mohammad16-Dec-14 21:40

Amr Mohammad

16-Dec-14 21:40

Thank very much for help. I'm trying to learn about recursive expression table however I cannot know how its really work Frown | :(

The Where clause you mentioned at the end of your post to be able to get the parent account and its child(s) too and that what I forgot to mention in the post because I did not know it at the time when I wrote the post what I need to get is the parent account and its child(s) account(s) and if those child(s) account(s) is/are a parent(s) for another child(s) account(s) I got them too Smile | :)

it seems stupid but this function is used within a procedure to get the balance of the account chart within the system.

Finally, I'm thanking you too much. could you help sending me a link to an explanation of the recursive CTE and how it is implemented

Re: Change cursor to common table

Richard Deeming17-Dec-14 2:05

Richard Deeming

17-Dec-14 2:05

I still think the query you posted is double-counting:

You get the total for account A;
You then loop through the child accounts:
- You get the total for account A.1 and its parent account (account A), and add it to the total;
- You get the total for account A.2 and its parent account (account A), and add it to the total;
Etc.

By the time you've finished, you've added the total for account A multiple times - once for each child account.

From your description, you just want to get the total balance for an account and its descendants, which means that your original query isn't correct.

My previous answer had a link to an article explaining recursive CTEs: How to use recursive CTE calls in T-SQL[^]
Google also returns the documentation on TechNet: http://technet.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx[^]
And a blog post: http://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

Re: Change cursor to common table

Amr Mohammad17-Dec-14 3:32

Amr Mohammad

17-Dec-14 3:32

Appreciating your help. Thanks very much I will review the function again as I'm not the one who wrote it however I'm trying to optimize a Stored Procedure performance which selects from a lot of tables + calling this function. the last time I executed the procedure it nearly took over 14 minutes without a single result while it was still executing Frown | :(

How can you tell if a SQL Server instance max'ed out bottleneck is Memory?

devvvy11-Dec-14 4:20

devvvy

11-Dec-14 4:20

hi - How can you tell if a SQL Server instance max'ed out bottleneck is Memory? It's on a 8GB box with two processors. Seems CPU ok. Memory max out the whole time while this big SELECT-INTO statement is running. Avg Disk Queue lenght ok - avg below 1.0 so doesn't appear disk/paging is causing problem.

However, fact from Task Manager memory keeps 7 GB out of 8GB over the whole time doesn't mean memory is the bottleneck? Or is it?

The same query runs on another SQL server with double the RAM finishes a few times quicker however.

Any advises? Thanks

dev

Re: How can you tell if a SQL Server instance max'ed out bottleneck is Memory?

Tim Carmichael11-Dec-14 4:23

Tim Carmichael

11-Dec-14 4:23

Check the configuration on the SQL Server instances to see what the maximum amount of memory is that is allocated to them. I'm don't remember where it is, but that is a starting point.

Re: How can you tell if a SQL Server instance max'ed out bottleneck is Memory?

M Riaz Bashir11-Dec-14 15:07

M Riaz Bashir

11-Dec-14 15:07

Thanks alot

It works Smile | :)

MySQL Export Problem

M Riaz Bashir10-Dec-14 22:46

M Riaz Bashir

10-Dec-14 22:46

Hi everyone

I am facing problem while I write the following syntax on command prompt:

c:\>mysqldump

SQL

Error is:   "mysqldump: unknown option '--no-beep'"

please let me know the solution.

(Riaz)

Re: MySQL Export Problem

Eddy Vluggen11-Dec-14 4:59

Eddy Vluggen

11-Dec-14 4:59

Try removing the option from the configuration file and try again.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Database Mail Not sending Email

Robymon8-Dec-14 20:56

Robymon

8-Dec-14 20:56

I want to send Email to Many Users from One Email Address. I did configured the Database Mail. After Scheduled this Job, It is sending Emails to some users and failing to some users. Following are the Error Message am getting from the Log.

1)The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-12-08T16:01:56).
Exception Message: Cannot send mails to mail server. (The operation has timed out.).

2)The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 3 (2014-12-08T16:02:05).
Exception Message: Cannot send mails to mail server. (Failure sending mail.).

I am getting this Time Out Message even after Updating the Time Out to 60 Sec

Do I have to have SQL Server Express installed?

jkirkerx30-Nov-14 11:31

jkirkerx

30-Nov-14 11:31

I wanted to add database services to my windows app, but didn't want to use SQL Server, Access or even a DBF file would of been fine.

So the code below works fine, creates a database file, but off the top of your head, will I need to have sql server installed on other client machines?

Dim m_connString As String = "Data Source=(LocalDB)\v11.0; Integrated Security=True;"
        Dim myConnection As SqlConnection = New SqlConnection(m_connString)

        Dim myQueryString As String = _
        "CREATE DATABASE STREAKWAVE ON PRIMARY " & _
        "( " & _
        "   NAME = STREAKWAVE, " & _
        "   FILENAME = '" & m_storagePath & "\STREAKWAVE.mdf', " & _
        "   SIZE = 10MB, " & _
        "   MAXSIZE = 500MB, " & _
        "   FILEGROWTH = 10% " &
        ") " & _
        " LOG ON " & _
        "( " & _
        "   NAME = STREAKWAVE_Log, " & _
        "   FILENAME = '" & m_storagePath & "\STREAKWAVE.ldf', " & _
        "   SIZE = 1MB, " & _
        "   MAXSIZE = 500MB, " & _
        "   FILEGROWTH = 10% " & _
        ") "

Re: Do I have to have SQL Server Express installed?

Richard MacCutchan30-Nov-14 21:30

Richard MacCutchan

30-Nov-14 21:30

If this code works then you are presumably using SQL CE or similar, which is on the local machine. That is fine as far as it goes, but if you wish to share the database with other clients then you will need SQL server.

Re: Do I have to have SQL Server Express installed?

jkirkerx2-Dec-14 10:21

jkirkerx

2-Dec-14 10:21

Sorry so late;

got wrapped up in a few projects

What a pain in the ?, I got it working finally, and found a 33 meg download for localDB.

But the speed is day and night, 20 secs using the DBF vs 1 sec using localDB

So VS2013 gives you the localDB, my users will have to install it.

Re: Do I have to have SQL Server Express installed?

Eddy Vluggen2-Dec-14 7:27

Eddy Vluggen

2-Dec-14 7:27

jkirkerx wrote:
off the top of your head, will I need to have sql server installed on other
client machines?

Only on the machine that hosts the database(s).

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Do I have to have SQL Server Express installed?

jkirkerx2-Dec-14 10:25

jkirkerx

2-Dec-14 10:25

Thanks Eddy

Just got it to work, create database, tables, records
make the con strings for that.

Better than the oleDB DBF I was going to use, which took 20 sec to write 49 records.
Finished the switch to localDB this morning.
I have never heard of localDB, but it seems handy for vb.net windows apps.

sql server import export wizard stored procedure?

iamvinod3426-Nov-14 18:57

iamvinod34

26-Nov-14 18:57

sql server import export wizard stored procedure

modified 27-Nov-14 2:42am.

Re: sql server import export wizard stored procedure?

Mycroft Holmes26-Nov-14 20:09

Mycroft Holmes

26-Nov-14 20:09

You need to expand your requirements as this is not a question.

Never underestimate the power of human stupidity
RAH

Re: sql server import export wizard stored procedure?

jschell27-Nov-14 6:02

jschell

27-Nov-14 6:02

SQL Server will not do that.

There are other tools associated with SQL Server that will.

(Naturally this answer takes a vast leap as to what the question as such was.)

Need to Truncate a table and populate the rest of the tables

Hari-CodeBlogger25-Nov-14 0:30

Hari-CodeBlogger

25-Nov-14 0:30

I have a .sql file. the file size is 401MB. i found that a table say 'pauseddetailes' which stores the values and more than 1,00,000 records which has to be archived periodically but it is not. Due to this i couldn't import the file in MySQL , phpMyadmin. i need to truncate the table 'pauseddetails' and then import the rest of the data in the db. This particular table is independent and doesn't have any foreign key constraint.

I tried increasing the value of the upload size , execution time and memory size in php.ini file still didn't work out.

Is there any possible way. Looking for the solution.

Thanks in advance.

Re: Need to Truncate a table and populate the rest of the tables

Shweta N Mishra26-Nov-14 4:35

Shweta N Mishra

26-Nov-14 4:35

what you are not able to do, Truncate or addition of new data ?

Whats the error ?

Mark the answer as accepted if that worked for you Smile | :)

.

And for down-voters please specify the reason to improve the solution Smile | :)

Joining three tables in sql server 2012

umair275624-Nov-14 4:06

umair2756

24-Nov-14 4:06

Dear All

we need some help. We have Three Tables in sqlserver2012

Master Table

OrderID PackageID CustomerName
1 1 Abc
2 2 Bcd
3 1 xyz

Child1 Table

OrderID ControlName
1 Row1COlumn1 (It Means Pant in Red Color is selected by user(relation with Child2 Table))
1 Row3Column1 (It Means Gown in Blue Color is selected by user(relation with Child2 Table))
1 Row4Column3 (It Means T Shirt in White Color is selected by user(relation with Child2 Table))
2 Row1Column2 (It Means Tie in Green Color is selected by user(relation with Child2 Table))
2 Row3Column1 (It Means Bow in Red Color is selected by user(relation with Child2 Table))

Child2 Table

PackageID Product Color1 Color2 Color3
1 Pant Red Green Blue
1 Shirt Blue Pink Purple
1 Gown Blue Black Yellow
1 T Shirt Red Green White
2 Tie Red Green White
2 Socks Red Green White
2 Bow Red Green White

We want to have result like

OrderID PackageID CustomerName Pant Gown T Shirt Tie Bow

1 1 ABC Red Blue White x x
Blue
2 2 Bcd x x x Green Red

DB Script
CREATE TABLE [dbo].[Child1](
[Child1ID] [int] NOT NULL,
[OrderID] [int] NULL,
[ControlName] [nvarchar](max) NULL,
CONSTRAINT [PK_Child1] PRIMARY KEY CLUSTERED
(
[Child1ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object: Table [dbo].[Child2] Script Date: 11/11/2014 6:06:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Child2](
[Child2ID] [int] NOT NULL,
[PackageID] [int] NULL,
[Product] [nvarchar](max) NULL,
[Color1] [nchar](10) NULL,
[Color2] [nchar](10) NULL,
[Color3] [nchar](10) NULL,
CONSTRAINT [PK_Child2] PRIMARY KEY CLUSTERED
(
[Child2ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object: Table [dbo].[MasterTable] Script Date: 11/11/2014 6:06:56 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MasterTable](
[OrderID] [int] NOT NULL,
[PackageID] [int] NULL,
[CustomerName] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_MasterTable] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (1, 1, N'Row1Column1')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (2, 1, N'Row3Column1')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (3, 1, N'Row4Column3')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (4, 2, N'Row1Column2')
GO
INSERT [dbo].[Child1] ([Child1ID], [OrderID], [ControlName]) VALUES (5, 3, N'Row3Column1')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (1, 1, N'Pant', N'Red ', N'Green ', N'Blue ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (2, 1, N'Shirt', N'Blue ', N'Pink ', N'Purple ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (3, 1, N'Gown', N'Blue ', N'Black ', N'Yellow ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (4, 1, N'T Shirt', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (5, 2, N'Tie', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (6, 2, N'Socks', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[Child2] ([Child2ID], [PackageID], [Product], [Color1], [Color2], [Color3]) VALUES (7, 2, N'Bow', N'Red ', N'Green ', N'White ')
GO
INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (1, 1, N'Abc')
GO
INSERT [dbo].[MasterTable] ([OrderID], [PackageID], [CustomerName]) VALUES (2, 2, N'xyz')
GO
USE [master]
GO
ALTER DATABASE [GarmentsTest] SET READ_WRITE
GO

Thanks and Best Regards Umair

Re: Joining three tables in sql server 2012

Simon_Whale24-Nov-14 4:56

Simon_Whale

24-Nov-14 4:56

This isn't how this site works, we will help you where you are stuck. But we dont supply solutions to a requirement.

But I would suggest you read up on inner joins[^] and left outer joins[^]

the syntax is similar across the different versions on SQL Server.

after you have read these and get stuck please show us your code and try to explain why its wrong and we will help you further.

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: Joining three tables in sql server 2012

Eddy Vluggen24-Nov-14 7:36

Eddy Vluggen

24-Nov-14 7:36

Next to that, I'd recommend normalizing the data-structure; non-normal tables often make querying more complicated.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.