Re: Crystal Report Query Engine error - Database Discussion Boards

martin_hughes5-Sep-07 4:08

5-Sep-07 4:08

I've done some googling and reading, and found a few solutions to this, but none that struck as being being particularly elegant, so thought I'd ask for some advice Smile | :)

I have a dynamically generated sql query which enables users to do keyword searches. The user can specify some wildcards to enable partail matching and so forth. It works, and is fairly secure - well I think it is, but I'm sure some crafty soul could hack it if they wanted to.

Anyway, while it works, I'd like to remove all of the querying logic from my application in to stored procedures (as much as a proof of concept and learning exercise than anything else), but I'm becoming unstuck by my lack of T-SQL knowledge.

The major problems I'm having are:
1) How to parse the input string and build the search criteria (including wildcards)
2) How to efficiently iterate over a record set and pull out records matching the criteria

As noted earlier, I have found some solutions - but the amount of SQL involved was fairly impressive, involving the building of temprorary tables, and multiple passes of the table etc. I just get the feeling that given a relatively large table (about 3 million records), performance is going to be an issue.

I'm not too concerned about being bound to SQL Server 2005 in this instance, so could anybody point me in the right direction of the techniques and language constructs I should look at? Woould this be an ideal place to use a CLR stored procedure?

Me: Can you see the "up" arrow?
User:Errr...ummm....no.
Me: Can you see an arrow that points upwards?
User: Oh yes, I see it now!

-Excerpt from a support call taken by me, 08/31/2007

Too much time consumed while binding datatable to grid

Nouman Bhatti5-Sep-07 1:42

Nouman Bhatti

5-Sep-07 1:42

i had a 70MB tab splitted file
i read that file in memory (string builder) and than i convert it in to a datatable
after that i set the datasource of grid equals to this table
but the problem is it takes more than one minute to show the data (the application takes around 1.5 minutes after i set the datasource property of the grid)

Re: Too much time consumed while binding datatable to grid

Michael Potter5-Sep-07 3:58

Michael Potter

5-Sep-07 3:58

Just load it directly (without using Datasource). Also, make sure you don't have any "look and feel" enabled on the grid prior to loading (sorting, sizing, etc.). Enable this after the load.

1) Disable look and feel.
2) Create rows equal to count of rows.
3) Fill rows/cells using for loop.
4) Enable look and feel.

I gave up using Datasource a long time ago.

Re: Too much time consumed while binding datatable to grid

Nouman Bhatti6-Sep-07 19:49

Nouman Bhatti

6-Sep-07 19:49

Thanks for your reply..
but can i have a sample application/code

bcz i tried it by writing a code as u mentioned
i had a 70MB file that contain more than 8,000,00 rows
the code is as follows

foreach(DataColumn dc in dt.Columns)
{
this.grd.Columns.Add(dc.ColumnName,dc.Caption);
}

Object[] obj = new Object[dt.Columns.Count];
foreach (DataRow dr in dt.Rows)
{
int i = 0;
foreach (object ob in dr.ItemArray)
{
obj[i] = ob;
i++;
}
grd.Rows.Add(obj);
i = 0;
}

Re: Too much time consumed while binding datatable to grid

Michael Potter7-Sep-07 3:21

Michael Potter

7-Sep-07 3:21

That is a lot of rows for a grid! You may want to look into dynamic loading (a few rows at a time). This is how I normally load a DataGridView:

foreach(DataColumn dc in dt.Columns)
{
    this.grd.Columns.Add(dc.ColumnName,dc.Caption);
}

int totalRows = dt.Rows.Count;
this.grd.Rows.Add(totalRows);
DataRow dr;
for (int i = 0; i < totalRows; i++)
{
    dr = dt.Rows[i];
    this.grd.Rows[i].Tag = dr; //For an editing reference
    this.grd.Rows[i].Cells[0].Value = dr["FirstColumn"].ToString();
    this.grd.Rows[i].Cells[1].Value = dr["SecondColumn"].Tostring();
}

Crystal Report Query Engine error

santhoshmohan4-Sep-07 23:48

santhoshmohan

4-Sep-07 23:48

I am using Crystal Reports in my application I made using Visual Studio.NET.
I installed my application on the client machine, with all the DLLs
required(added the merge modules), the engine and the rest. When I start my
application and, from it, try to open up a report I get an exception "Query
engine error: C:\WINDOWS\Temp\temp_*******.rpt" with some wierd numbers
instead of *******. Can anyone tell me what the problem is here and how to
solve it. I am running the application on Windows 98.

Re: Crystal Report Query Engine error

Frank Kerrigan5-Sep-07 0:47

Frank Kerrigan

5-Sep-07 0:47

What is the error message that is normally a bit of a clue?

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: Crystal Report Query Engine error

Colin Angus Mackay5-Sep-07 1:21

Colin Angus Mackay

5-Sep-07 1:21

santhoshmohan wrote:
some wierd numbers

Define "weird numbers"? How is one number any more or less weird than another. For example, is 8 more or less weird than 17? And what about 42? How weird is that?

Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, db4o: An Embeddable Database Engine for Object-Oriented Environments, Mock Objects, SQL Server CLR Integration, Reporting Services ...

My website

Invoking function inside a Stored procedure affect perfomance??

Vipin.d4-Sep-07 23:06

Vipin.d

4-Sep-07 23:06

Hi all,
I would like to know something about performance tuning.

My doubt is I have A stored procedure for selecting data from a traxn' table.In that SELECT command, i want to invoke a UDF(find its count or sum like...).
Is it affects the perfomance of selecting data from DataBase???

Thnkx in advance

All I ever wanted is what others have....
CrazySanker

Re: Invoking function inside a Stored procedure affect perfomance??

Vasudevan Deepak Kumar4-Sep-07 23:15

Vasudevan Deepak Kumar

4-Sep-07 23:15

I would suggest you could have first glimpsed at the various typographical and lexical gaps in the post that you have made.

1) Even whilst mobile phones are now equipped with state-of-the-art dictionaries to dissuade user from cryptic ambiguous text messaging, there seems to be a little-to-more ambiguities over here:

Vipin dev wrote:
traxn'

Vipin dev wrote:
Thnkx

Did it mean '~~HIGH VOLTAGE~~ <small>TRACTION</small>' or 'TRANSACTION'?

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: Invoking function inside a Stored procedure affect perfomance??

Vipin.d5-Sep-07 4:35

Vipin.d

5-Sep-07 4:35

Sorry Mr.Vasudevan

All I ever wanted is what others have....
CrazySanker

Re: Invoking function inside a Stored procedure affect perfomance??

Frank Kerrigan5-Sep-07 0:56

Frank Kerrigan

5-Sep-07 0:56

Using a UDF within a stored procedure can have negative performance effects as it must run for every record it pulls form the database.

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Filter the Null Column

RichardBlare4-Sep-07 22:59

RichardBlare

4-Sep-07 22:59

Hi , here's my query result.
I want to filter out the column with NULL values. How can I do it ?

Dept Name Newyork LA Washington City1 SanFrn City2

Department1 0 0 0 NULL 1 NULL
Department2 4 0 0 NULL 2 NULL
Department3 0 0 0 NULL 0 NULL

Thanks in advance,

Re: Filter the Null Column

Vipin.d4-Sep-07 23:07

Vipin.d

4-Sep-07 23:07

SELECT DeptName,ISNULL(NEWYORK,0),ISNULL(LA,0) from MYTABLE WHERE....

All I ever wanted is what others have....
CrazySanker

Re: Filter the Null Column

RichardBlare4-Sep-07 23:34

RichardBlare

4-Sep-07 23:34

Dear Vipin dev,
Actually, I just want to remove the columns with NULL value. If I use ISNULL(NEWYORK,0), the column still in the result set and column name is 0.
Is there anyway I can do it ?

Thanks in advance,

Re: Filter the Null Column

Vasudevan Deepak Kumar4-Sep-07 23:40

Vasudevan Deepak Kumar

4-Sep-07 23:40

There is a light refinment in display that is outlined here. Hope that would help you to some extent:

http://www.sql-server-performance.com/faq/null_values_resultset_p1.aspx[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Re: Filter the Null Column

kumarrumak5-Sep-07 0:48

kumarrumak

5-Sep-07 0:48

hi check out this

select * from tablename where col is null or col='NULL'

Re: Filter the Null Column

Frank Kerrigan5-Sep-07 1:00

Frank Kerrigan

5-Sep-07 1:00

I think what you want is COALESCE

select COALESCE(fieldname, 0)

COALESCE will select the first non null from a list so you can even have

select COALESCE(field1,field2,field3,field4, 0)

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

DST Implementation

ramdil4-Sep-07 22:46

ramdil

4-Sep-07 22:46

Hi
How can i check Day Light saving time in sql.
I know there is a inbuilt function called ISDST in C# , but i want that check to be done in Sql procedure.Thanks in Advance

Regards
DilipRam

Re: DST Implementation

Vasudevan Deepak Kumar4-Sep-07 23:44

Vasudevan Deepak Kumar

4-Sep-07 23:44

[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

Sql server Date Diff

Praveen0294-Sep-07 21:35

Praveen029

4-Sep-07 21:35

Hi, all

select convert(varchar,(datediff(dd,'1979-09-04 12:34:50.603',getdate())/365))

here getdate()='2007-09-05 12:34:50.603'

I need find the age of employee but this result is showing 28 instead of 27
because till one day is required to complete 27...

Praveen Kumar

Re: Sql server Date Diff

Frank Kerrigan5-Sep-07 1:01

Frank Kerrigan

5-Sep-07 1:01

How are you getting the age, Are you using DATEDIFF ?

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Re: Sql server Date Diff

Michael Potter5-Sep-07 5:48

Michael Potter

5-Sep-07 5:48

Try something like this for a more accurate result:

SELECT
    YEAR(GETDATE()) - YEAR('1979-09-04 12:34:50.603') - 
    CASE
        WHEN DATEPART(dayofyear,GETDATE()) < DATEPART(dayofyear,'1979-09-04 12:34:50.603') THEN 1
        ELSE 0
    END AS Age

Re: Sql server Date Diff

Frank Kerrigan5-Sep-07 22:56

Frank Kerrigan

5-Sep-07 22:56

Why do you use

<br />
<br />
DATEDIFF(YY, @tartdate, getdate())<br />
<br />
<br />
<br />

Grady Booch: I told Google to their face...what you need is some serious adult supervision. (2007 Turing lecture)

http://www.frankkerrigan.com/[^]

Distinct Select Query

Shajeel4-Sep-07 21:23

Shajeel

4-Sep-07 21:23

Hi I have table with three columns
col1, col2, col3
i need distinct values of col1 with any values in col2 and col3, eg if data is
1, A, A
1, B, B
2, C, C
i need my query to bring
1, A, A or 1, B, B (only one not both)
2, C, C
i need query for this. i cannot use select dintinct col1, col2, col3 from table due to 2nd row. i am using sql server.

Regards
Shajeel

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