Database

1. Read the question carefully
2. Understand that English isn't everyone's first language so be lenient of bad spelling and grammar
3. If a question is poorly phrased then either ask for clarification, ignore it, or mark it down. Insults are not welcome
4. If the question is inappropriate then click the 'vote to remove message' button

1. Choose the correct forum for your message. Posting a VB.NET question in the C++ forum will end in tears.
   
   
2. Be specific! Don't ask "can someone send me the code to create an application that does 'X'. Pinpoint exactly what it is you need help with.
   
   
3. Keep the subject line brief, but descriptive. eg "File Serialization problem"
   
   
4. Keep the question as brief as possible. If you have to include code, include the smallest snippet of code you can.
   
   
5. Be careful when including code that you haven't made a typo. Typing mistakes can become the focal point instead of the actual question you asked.
   
   
6. Do not remove or empty a message if others have replied. Keep the thread intact and available for others to search and read. If your problem was answered then edit your message and add "[Solved]" to the subject line of the original post, and cast an approval vote to the one or several answers that really helped you.
   
   
7. If you are posting source code with your question, place it inside <pre></pre> tags. We advise you also check the "Encode "<" (and other HTML) characters when pasting" checkbox before pasting anything inside the PRE block, and make sure "Use HTML in this post" check box is checked.
   
   
8. Be courteous and DON'T SHOUT. Everyone here helps because they enjoy helping others, not because it's their job.
   
   
9. Please do not post links to your question into an unrelated forum such as the lounge. It will be deleted. Likewise, do not post the same question in more than one forum.
   
   
10. Do not be abusive, offensive, inappropriate or harass anyone on the boards. Doing so will get you kicked off and banned. Play nice.
    
    
11. If you have a school or university assignment, assume that your teacher or lecturer is also reading these forums.
    
    
12. No advertising or soliciting.
    
    
13. We reserve the right to move your posts to a more appropriate forum or to delete anything deemed inappropriate or illegal.

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);
 
    // filter file types
    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        _package = app.LoadPackage(dtsPackPath, null);
 
        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
        _package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
            + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
                    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
                    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";
 
        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        var res = _package.Execute();
 
        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }
    }
 
    while (Console.Read() != 'q') ;
}

private static void OnCreated(object source, FileSystemEventArgs e)
{
    // Specify what is done when a file is changed, created, or deleted.
    // get the file's extension
    string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
    string directoryFullPath = Path.GetDirectoryName(e.FullPath);
 
    if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
    {
        Package _package = null;
        Application app = new Application();
        string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
        string sqlServerConnectionString = ConfigurationManager.AppSettings["SqlServerConnectionString"];
        _package = app.LoadPackage(dtsPackPath, null);
 
        _package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
 
        //_package.Variables["User::SourceFileName"].Value = e.FullPath;

        //_package.Variables["User::FileName"].Value = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString()
        //    + DateTime.Now.Day.ToString()
        //    + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //    + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";

        _package.Connections["BSCWD725128.DTS_Testing"].ConnectionString = sqlServerConnectionString;
 
        var res = _package.Execute();
 
        if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
        {
            string message = "Package Executed Successfully....";
        }
 
        //_package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
        //    + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
        //            + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
        //            + DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";
    }
 
    while (Console.Read() != 'q') ;
}

ALTER PROCEDURE [EventPortion].[24Start]
 AS  
 
IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
 
IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
 
IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null 
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
 

SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
 
While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
 
Begin
WAITFOR DELAY '00:00:01.000';
End
 
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
 

While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3

ALTER PROCEDURE [EventPortion].[24Start]
 AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3

private void ParseStartProcedure(string spText)
        {            
            spText = spText.Replace("\r", " ");            
            spText = spText.Replace("\t", " ");           
            spText = spText.Trim();
 
            // Take everything after the first "AS"
            string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
            string[] queryLines = Regex.Split(queryPieces[1], "\n"); //split on the new line character

            for (int i = 0; i < queryLines.Length; i++)
            {
                if (queryLines[i] != "")
                {
                    string query = queryLines[i];
                    SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException ex)
                    {
                        //TODO: Add more robust error handling to handle errors other than "Failed to drop, etc.
                        string message = ex.Message;
                    }
                }
            }            
        }

Item(Id_Item,ItemNo,ItemName)
 Lot(Id_Lot,Item_Id,LotNo)
 Process(Id_Process, ProcessNo, ProcessName)
 Lot_Process(Id,Lot_Id,Process_Id)
 Material(Id,Process_Id,MaterialName, Quantity)

CREATE TABLE ps_data.test (
  Test_ID int(11) NOT NULL AUTO_INCREMENT,
  Idx int(11) NOT NULL,
  Grp int(11) DEFAULT NULL,
  PRIMARY KEY (Test_ID)
)
ENGINE = INNODB
AUTO_INCREMENT = 10
AVG_ROW_LENGTH = 1820
CHARACTER SET utf8
COLLATE utf8_unicode_ci;

INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(1, 1, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(2, 2, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(3, 3, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(4, 4, 1);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(5, 5, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(6, 6, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(7, 7, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(8, 8, 2);
INSERT INTO ps_data.test(Test_ID, Idx, Grp) VALUES
(9, 9, 2);

UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test) tbl WHERE tbl.Grp=1)
WHERE Test_ID=1 
 
UPDATE test
SET Idx=(SELECT COALESCE(MAX(tbl.Idx), 0)+1 FROM (SELECT * FROM test WHERE Grp=1) tbl)
WHERE Test_ID=1 
 
UPDATE test t, (SELECT * FROM test WHERE Grp=1) tbl
SET t.Idx= (SELECT COALESCE(MAX(tbl.Idx), 0)+1)
WHERE t.Test_ID=1 

Declare	@FundCodes VarChar(Max)='Bateleur|Tower Fund',--Tower Fund
		@StartDate DateTime='2016/04/20',
		@EndDate DateTime='2016/04/29',
		@Zero integer=1,
		@LongShortAll integer = 0, --0 for all, 1 for long value, 2 for short value
		@PhysEff integer = 0 --0 for physical, 1 for Effective

 
		--check the fulldate 
		select fulldate into #TableDates 
		from AssetData.dbo.Calendar cal where cal.FullDate between @StartDate and @EndDate
		and IsWeekDay = 1 and IsHoliday = 0
	
		-- SET NOCOUNT ON added to prevent extra result sets from
		-- interfering with SELECT statements.
		SET NOCOUNT ON;
	     
		declare @NextDate DateTime
		set @NextDate = @StartDate
		declare @TempValues table(FundCode varchar(200),EffectiveDate DateTime, Value Float)	
	
		declare @fundcodestable table (ID int, fundcode varchar(300))
		declare @Val float
 
		--Date Counter
		declare @DateCounter int = 0
		select @DateCounter = count(Fulldate) from #TableDates
		declare @Date date
				
		while @DateCounter <> 0 
	
		begin
 
		set @Date = (select top 1 fulldate from #TableDates order by 1)
	
		--fundcode
		insert into @fundcodestable
		select	pn, s
		from	StagedFundReportingData..FnxSplit('|', @fundcodes)
 
		--fundcode Counter
		Declare @counterFundId int = 0
		select @counterFundId = count(fundcode) from @fundcodestable
		declare @fundC varchar(300) 
		
		while @counterFundId <>0 and @NextDate<=@EndDate
		begin
			set @fundC = (select top 1 fundcode from @fundcodestable)
				Set @Val = 0
				if @Zero = 0
				   begin
						if @LongShortAll = 0 begin
							exec GetFundValue @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly @fundC, @NextDate, @Val output
								end
							end
						end
					end
					else
					begin
						If @LongShortAll = 0 begin
							exec GetFundValue_ZeroFutures @fundC, @NextDate, @Val output
						end else begin
							if @LongShortAll = 1 begin
								if @PhysEff = 0 begin
									exec GetFundValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueLongOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end else begin
								if @PhysEff = 0 begin
									exec GetFundValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end else begin
									exec GetFundUnderlyingEffectiveValueShortOnly_ZeroFutures @fundC, @NextDate, @Val output
								end
							end
						end
					end
					Set @Val = isnull(@Val,0)
 
					if @Val <> 0
					begin
						insert into @TempValues (FundCode,EffectiveDate, Value) Values (@fundC,@NextDate, @Val)
					end
 
					if datepart(dw,@NextDate) = 7
					begin
						Set @NextDate = DATEADD(Day,2,@NextDate)
					end
					else
					begin
						if datepart(dw,@NextDate) = 6
						begin
							Set @NextDate = DATEADD(Day,3,@NextDate)
						end
						else
						begin
							Set @NextDate = DATEADD(Day,1,@NextDate)
						end 
					end
				  end
			    delete #TableDates where FullDate = @NextDate
		      select @DateCounter = @DateCounter-1
			 --delete @fundcodestable where fundcode = @fundC
		  --     set @counterFundId = @counterFundId - 1  	
		 end
			
 
		Select * from @TempValues order by FundCode
		drop table #TableDates

How to speedup the execution of a query in Sql Server?

EmployeeId INT, FirstName VARCHAR, SurName VARCHAR, SexId INT 

SexId INT, Sex VARCHAR
-- Values inserted
-- 1, Male
-- 2, Female

EmployeeId INT, Contact VARCHAR -- may or may not be null AND can contain multiple values

SELECT ED.EmployeeId, ED.FirstName, ED.SurName, GROUP_CONCAT(EC.Contact) 
FROM EmployeeDetails ED, EmployeeSex ES, EmployeeContacts EC
WHERE ED.SexId = ES.SexId AND ED.EmployeeId = EC.EmployeeId AND ED.EmployeeId = 'emp_password';

Hi.
 
I have to choose between MySQL and PostgreSQL. I like use MS SQL, but it expensive for the customer.
Database have about 100 tables with max. 200 thousands rows.
Front application is .NET (winform) ,dataset or entity framework.
 
It's critical application, reliability is required.
 
Please advice me.
 
Karol