Database

14-Aug-04 4:43

I have the following query

<br />
SELECT *<br />
FROM (((((SK_SKILLSINAGES AS sia LEFT JOIN SK_SKILLS AS s ON (sia.SkillCode = s.SkillCode) OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode)) INNER JOIN SK_CATEGORY AS c ON sia.CategoryCode = c.CategoryCode) INNER JOIN SK_CATEGORYGROUPS AS cg ON (sia.GroupCode = cg.GroupCode) AND (cg.GroupCode = c.GroupCode)) INNER JOIN AG_AGESINERAS AS ae ON sia.AgeCode = ae.AgeCode) INNER JOIN AG_ERA AS e ON ae.EraCode = e.EraCode) INNER JOIN PR_DEVELOPMENTPOINTS AS pd ON (sia.CategoryCode = pd.CategoryCode) AND (sia.GroupCode = pd.GroupCode)<br />
WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null) OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null) AND  sia.CategoryCode = c.CategoryCode AND sia.GroupCode = cg.GroupCode AND sia.AgeCode = ae.AgeCode) AND ae.Description = 'Middle Ages' AND e.Description = 'Rolemaster' AND pd.ProfessionCode = 'ANIMIST'<br />
ORDER BY sia.AgeCode, sia.GroupCode, sia.CategoryCode, sia.SkillCode, sia.SubSkillCode;<br />

I have created an index for each of the fields that are being joined and have created an multiple field index for the sort criteria in SK_SKILLSINAGES however this query still takes a long time to load; around ten seconds. If I remove the ORDER BY sort criteria the query is much much faster. Might I be forgetting something crucial that could help speed up this query. It all seems dependant on the sort.

Thanks!

Colin Angus Mackay15-Aug-04 10:26

15-Aug-04 10:26

I would suggest you run this through the Index Tuning Wizard. Blindly creating indexes isn't really the way to go.

"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Can't manage to P/Invoke that Win32 API in .NET? Why not do interop the wiki way!

brdavid15-Aug-04 11:47

15-Aug-04 11:47

I see that the Index Tuning Wizard is a relic of Microsoft SQL Server database, something I don't have. I am using Microsoft Access and access the jet database through ADO. This could be the reason in itself but with that I don't know. Without having to install an SQL server database package might there be another solution.

I actually managed to get a few indexes in place to get a sorted result so I can remove the ORDER BY stuff, however the query still takes a long time to load. When the statement is calling a simple table to return roughly the same number of records it goes fast, but when I introduce JOIN statements, it gets slow.

Colin Angus Mackay15-Aug-04 12:10

15-Aug-04 12:10

brchris wrote:
I see that the Index Tuning Wizard is a relic of Microsoft SQL Server database

I wouldn't describe it as a "relic"

brdavid15-Aug-04 15:05

15-Aug-04 15:05

True. I meant it in a good way. Wink | ;)

Anonymous23-Aug-04 7:58

Anonymous

23-Aug-04 7:58

Try the following:

SELECT *
FROM SK_SKILLSINAGES AS sia
INNER JOIN AG_AGESINERAS AS ae
	ON sia.AgeCode = ae.AgeCode
	AND ae.Description = 'Middle Ages'
INNER JOIN SK_CATEGORYGROUPS AS cg
	ON sia.GroupCode = cg.GroupCode
INNER JOIN SK_CATEGORY AS c
	ON sia.CategoryCode = c.CategoryCode
INNER JOIN AG_ERA AS e
	ON ae.EraCode = e.EraCode
	AND e.Description = 'Rolemaster'
INNER JOIN PR_DEVELOPMENTPOINTS AS pd
	ON sia.CategoryCode = pd.CategoryCode
	AND sia.GroupCode = pd.GroupCode
	AND pd.ProfessionCode = 'ANIMIST'
LEFT JOIN SK_SKILLS AS s
	ON (sia.SkillCode = s.SkillCode)
	OR (sia.SkillCode = s.ParentSkillCode AND sia.SubSkillCode = s.SkillCode))
WHERE ( (sia.SubSkillCode Is Null AND s.ParentSkillCode Is Null)
	OR (sia.SubSkillCode Is Not Null AND s.ParentSkillCode Is Not Null)
ORDER BY sia.AgeCode, sia.GroupCode, sia.CategoryCode, sia.SkillCode, sia.SubSkillCode;

I have tried to simplify and reorder the SQL. You have a number of joins that restrict the number of records returned (e.g. your "pd.ProfessionCode = 'ANIMIST'" clause). You might want to try moving the more restrictive tables to the top of the "from" clause.

My guess is that the "or" statements involved with the link to SK_SKILLS are the big performance drain (because many databases won't bother to use the indexes) You can test this by removing that part of the statement). A possible way of getting around the "OR" problem is to use a union-join:

SELECT * FROM tables
	WHERE sia.SkillCode = s.SkillCode
UNION ALL
SELECT * FROM tables
	WHERE sia.SkillCode = s.ParentSkillCode
	AND sia.SubSkillCode = s.SkillCode
ORDER BY <columns>

This approach allows the database engine to use separate indexes for the two different links to the skills table.

Hope this helps.
Andy Harman

brdavid28-Aug-04 6:16

Re: C# SQL INSERT String???

28-Aug-04 6:16

You're right about the OR in the SQL. It seems if I remove it, the query goes through faster. Using the UNION query does solve the problem too. Thanks for the reply!

C# SQL INSERT String???

gman4413-Aug-04 12:16

gman44

13-Aug-04 12:16

I have a slight problem inserting the proper result into an MS-SQL table. The result should be <14> however all I get is PRE. BTW, the SQL command not broken appart like you see it on this limited width posting..........

I thought using single quotes desiganted a 'string'? Help........

string pattern1 = @"(<\d{2,3}>)";

Regex r1 = new Regex(pattern1, RegexOptions.Compiled);

Match PRE = r1.Match(stringOfData);
{
try
{
connection1.Open();
SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES ('PRE')";
SqlDataAdapter1.SelectCommand.ExecuteNonQuery();
connection1.Close();
}
catch(SqlException error)
{
MessageBox.Show(error.Message.ToString());
}
listBox1.Items.Add(PRE.ToString());
}
}

Colin Angus Mackay13-Aug-04 12:47

Re: C# SQL INSERT String???

13-Aug-04 12:47

Ummmm... Are you trying to say that the VALUES('PRE') in your CommandText is supposed to be the result of Match PRE = r1.Match(stringOfData) ???

gman4415-Aug-04 4:03

gman44

15-Aug-04 4:03

Yes, this particular Regex matches numbers between < >. For instance <14>, however only PRE is written to the database........

Re: C# SQL INSERT String???

S Sansanwal15-Aug-04 17:07

S Sansanwal

15-Aug-04 17:07

You are inserting hardCoded "PRE" value rather than variable PRE

Change your sql to

SqlDataAdapter1.SelectCommand.CommandText = "INSERT INTO
tblMyUdpServer (col_PRE) VALUES (" + PRE.Value + ")";

Sanjay Sansanwal
www.sansanwal.com

SELECT in UPDATE statement

blopf13-Aug-04 9:28

blopf

13-Aug-04 9:28

Hello All,
I have a problem that I cannot seem to solve. My guess is that I am thinking wrong. The problem is probably really simple but I cannot seem to solve it. I have two tables, one called test_user and one called test_answer. (Below all types in the tables are integers)

test_user looks like this:
user_id,time_answered,+some other statistics
1 0
2 0
3 0

test_answer looks like this:
test_id,user_id,answered
0 1 1
0 2 1
0 3 0
1 1 1
1 2 1
1 3 0
3 1 1
3 2 0
3 3 0

Basicly test_answered contains a 1 in the answered if the user has answered a question on test test_id.

What I want to do is sum up all the answers and update the test_user table. If I do this SQL-query then I get the table I want to update test_user with.

select user_id,sum(answered) as sum from test_answer where answered = 1 group by user_id

(The output will be:
user_id,sum
1 3
2 2 )

Is there a simple way to update the test_user tables without doing a select into (and destroying all other data I have in the table.)

As I told you this is probably an super simple question but I cannot get it right?

hope you guys can help me!

Colin Angus Mackay13-Aug-04 12:50

13-Aug-04 12:50

What do you mean it is destroying all the other data in the table? What is your existing UPDATE query?

blopf15-Aug-04 4:19

blopf

15-Aug-04 4:19

What I mean is that if I do the select with an select into statement it will create a new table for me with the result. I dont have an update statement, because all the ones I tried generates errors Frown | :(

Mike Dimmick15-Aug-04 9:11

Mike Dimmick

15-Aug-04 9:11

UPDATE test_user
SET no_answered = SUM(answered)
FROM test_answer
WHERE user_id = test_answer.user_id

I haven't tested this, but it's along these lines.

Stability. What an interesting concept. -- Chris Maunder