MDX Query Above 8000 Characters

May 23, 2006

Hi,

I have a MDX query which is of an aprox length of 10000 characters. I
have to execute the query from within the stored procedure in sql. To
run this query I use the openrowset method.

If the length of my query is less than 8000 characters my query
executes perfectly, but the moment it exceeds 8000 characters it stop
working. Please suggest a solution for the same.

Sample Code:

declare @mdxqry varchar(8000)
declare @SearchCond varchar(8000)

set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '

set @mdxqry = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
' +
'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @mdxqry = @mdxqry +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''

set @mdxqry = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

exec(@mdxqry)

I have already tried splitting my query into smalled chunks and
executing it, but still I face the same problem.

This is how I have Done it:

declare @mdxqry1 varchar(8000)
declare @mdxqry2 varchar(8000)
declare @SearchCond varchar(8000)

set @SearchCond = @SearchCond + '
[ProductsAccounts].CurrentMember.properties("AS Date") <= "' + @TDate
+ '" '

set @mdxqry1 = '''WITH ' +
'MEMBER [Measures].[Difference] as ''''[Measures].[Expected Interest
Amount] - [Measures].[Adjusted Interest]'''' ' +
'MEMBER [Measures].[Loan Closed within Report Period] as ' +
'''''iif(cdate([ProductsAccounts].CurrentMember.properties("Closed
Date")) < cdate("' + @ToDate + '"), "Yes", "No")''''' +
'MEMBER [Measures].[ClosedBeforeLastInstallment] as
''''iif([Measures].[Loan Closed Before Last Instal]=1, "Yes", "No")''''
'

set @mdxqry2 = 'SELECT ' +
'{[Measures].[Expected Interest Amount], [Measures].[Adjusted
Interest], [Measures].[Difference], ' +
'[Measures].[Zero Interest Transactions],
[Measures].[ClosedBeforeLastInstallment], ' +
'[Measures].[Loan Closed within Report Period]} ON 0, '

set @mdxqry2 = @mdxqry2 +
'{Filter([ProductsAccounts].[Account Id].Members, (' + @SearchCond +
'))} on 2, ' +
@BranchFilter +
'FROM InterestAnalysis'''

set @mdxqry2 = 'SELECT a.* FROM
OpenRowset(''MSOLAP'',''DATASOURCE="SERVERNAME"; Initial
Catalog="DATABASENAME";'',' + @mdxqry + ') as a'

exec(@mdxqry1 + @mdxqry2)

Thanks in Advance

Charu

View 5 Replies


ADVERTISEMENT

MS SQL Passing In More Than 8000 Characters

Oct 14, 2007

if a user chooses to request a lot of customers to report on say from a multi select listbox - what is the best way to pass this list to my stored proc? Looking for suggestions.
thanks,
 

View 3 Replies View Related

Dealing With More Than 8000 Characters

Nov 14, 2005

In SS 2000 it seems that there is no variable data type that can hold more than 8000 characters (varchar) or 4000 unicode characters (nvarchar). I've seen posts where multiple variables are spliced together to extend this limit. I am looking at performing string manipulations in an sproc and I need to be able to deal with the full 2GB/1GB limit of text and ntext field types. Is this possible? How do you deal with that?

View 14 Replies View Related

VARCHAR(MAX) Only Contains 8000 Characters?

Nov 25, 2007



Hi,

I had a VARCHAR(MAX) parameter declared in my stored procedure and trying to concatenat single column from a table which has~500 rows into a string and keep in this variable, if i am not mistaken, i read that the VARCHAR(MAX) actually can hold up to 2GB of data, so it make me confuse why the variable which i declared as MAX size, can only hold up 8000 characters, any idea?

Regards,
Derek

View 10 Replies View Related

Unable To Submit More Than 8000 Characters With Stored Procedure

Jul 20, 2005

Hi all,I have a internet page written in asp to submit into authorscurriculum vitae publications (title, author, year, etc.).If the author submit less than 8000 characters it functions OK, but Ifthe author try's to submit more than 8000 characters the asp page doesnot return an error but the text is not saved in the database or,sometimes, it returned a "Typ mismatch" error.Here is the sp:---------------------------------------------------------------------CREATE PROCEDURE sp_CV_publications(@formCommandnvarchar(255)='process',@id numeric=null,@id_personint=null,@typPubID tinyint= NULL,@publicationstext=null)ASif @formCommand='process'beginINSERT INTO CV_publications(idperson,typPubID,publications)VALUES (@id_person@typPubID,@publications);select 1 as status, @@IDENTITY AS insertedID, * FROMCV_publications WHERE id=@@IDENTITYend----------------------------------------------------------------------------The server is running IIS5 and SqlServer 2000Any ideas ???

View 1 Replies View Related

Passing A List Of Numbers To A Stored Procudure, Having A Size More Than 8000 Characters

Aug 1, 2007

Hi..

I m working on MS SQL Server 2000.
I am trying to pass a list of numbers to a stored procedure to be used with 'IN()' statement.

I was doing something like..

Create Procedure proc

(

@Items varchar(100) --- List of numbers
)
AS Begin

Declare @SQL varchar(8000)
Set @SQL =
'
Select Query......
Where products IN (' + @items + ') '
'
Exec (@SQL)


This stored procedure is working fine, but when i m adding more required stuff to that, the size exceeds 8000, & it gives the error "Invalid operator for data type. Operator equals add, type equals text."

Can any1 please help me out with this ASAP??






View 4 Replies View Related

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!

Dec 19, 2005

Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!
I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.
I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string  to  @Insertcontent , the stored procedure can't be launch! why?
create procedure Hellocw_ImportBookmark  @userId         varchar(80),  @FolderId       varchar(80),  @Insertcontent  nvarchar(max)
as  declare @contentsql nvarchar(max);  set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+                    @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'')  where userId='''+@userID+'''';  exec sp_executesql @contentsql;

View 2 Replies View Related

Problem About Pass A Big String (over 8000 Characters) To A Variable Nvarchar(max) In Stored Procedure In SQL 2005!

Dec 19, 2005

Problem about pass a big string (over 8000 characters) to a variable nvarchar(max) in stored procedure in SQL 2005!

I know that SQL 2005 define a new field nvarchar(max) which can stored 2G size string.

I have made a stored procedure Hellocw_ImportBookmark, but when I pass a big string  to  @Insertcontent , the stored procedure can't be launch! why?

 

 

 

 

----------------------13-------------------------------------
create procedure Hellocw_ImportBookmark
  @userId         varchar(80),
  @FolderId       varchar(80),
  @Insertcontent  nvarchar(max)

as
  declare @contentsql nvarchar(max);
  set @contentsql=N'update cw_bookmark set Bookmark.modify(''declare namespace x="http://www.hellocw.com/onlinebookmark"; insert '+
                    @Insertcontent+' as last into (//x:Folder[@Id="'+@FolderId+'"])[1]'')  where userId='''+@userID+'''';
  exec sp_executesql @contentsql;

View 6 Replies View Related

Overcoming The Openquery 8000 Char Limit &&amp; Problem With Using Call Mystoredproc() In Open Query

Oct 10, 2007

Does anyone know why this statement would fail? I have created my own assembly which is on the server and when i run the mdx query call myassembly.mystoredproc() it returns data, but now when I use call myassembly.mystoredproc() it returns an error.

using an mdx query this works fine
call AsmTest.Asm.Analysis.TestClass.NameMe()

When using openquery i get an error
select * from openquery(NV, 'call AsmTest.Asm.Analysis.TestClass.NameMe()' )

OLE DB provider "MSOLAP.3" for linked server "NV" returned message "Prepare is not safe during execution of the NameMe stored procedure.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "call AsmTest.Asm.Analysis.TestClass.NameMe()" for execution against OLE DB provider "MSOLAP.3" for linked server "NV".




linked server definition

USE master
GO

/* Add new linked server */
EXEC sp_addlinkedserver
@server='NV', -- local SQL name given to the linked server
@srvproduct='', -- not used
@provider='MSOLAP.3', -- OLE DB provider (the .2 means the SQL2K version)
@datasrc='nvsifwfp', -- analysis server name (machine name)
@catalog='ARTSDW' -- default catalog/database

View 1 Replies View Related

SQL Query For Special Characters

Apr 18, 2007

On the bills that our system generates there is a comments field that users fillout. We have occasional problems with special characters in the text messing up the validation code. Does anyone know of a query that can identify special characters in a text field? Like carriage returns, tabs, etc.?
 Thanks, Dave

View 1 Replies View Related

Replace Characters In A Query

Dec 3, 2002

Hi All,

I have a column in one of my tables and I need to display only Alphabets and Numbers of this column, preferably in one single query.

Eg: Column Value --> This is 4th value with no *, &, # symbols.

I want this to be displayed as

Thisis4thvaluewithnosymbols

(All spaces and characters other than alphabets and numerics are gone)

Pleas help.

Thanks

View 1 Replies View Related

SQL Query Help - Accented Characters

Oct 10, 2007

Hello, I am trying to filter out any accented characters from a field in a select statement. I want to keep the accented version in the database, but have it convert when exporting.
Here is a sample of the php code that I used to use to accomplish this...

Code:


$shipfirstname = ereg_replace("[àâäåãáÂÄÀÅÃÁæÆ]", "a", $shipfirstname);
$shipfirstname = ereg_replace("[çÇ]", "c", $shipfirstname);
$shipfirstname = ereg_replace("[éèêëÉÊËÈ]", "e", $shipfirstname);
$shipfirstname = ereg_replace("[ïîìíÏÎÌÍ]", "i", $shipfirstname);
$shipfirstname = ereg_replace("[ñÑ]", "n", $shipfirstname);
$shipfirstname = ereg_replace("[öôóòõÓÔÖÒÕ]", "o", $shipfirstname);
$shipfirstname = ereg_replace("[ùûüúÜÛÙÚ]", "u", $shipfirstname);
$shipfirstname = ereg_replace("[ÿ]", "y", $shipfirstname);


So far, I have this sql code, but I will have to use a number of nested REPLACE statements to accomplish the same thing.

Code:

(SELECT REPLACE(ShipFirstName, 'à', 'a'))AS ShipFirstName,



Does anyone know an easier way to filter out these characters?

View 5 Replies View Related

Query Return Funny Characters

Apr 18, 2006

I run a query in QA which return funny characters (suppose to be chinese characters). I try saved as CSV and open in excel, still it remain as those funny characters... What can I do to get those output into the chinese character. It doesn't need to be in QA... but I need it in Excel.
Thanks

View 2 Replies View Related

Truncation Of Characters In A Query Result

Aug 31, 1999

Hi!
The product description table contains a field,
long_desc varchar(8000). Is there any way to get the entire description? The query, 'select long_desc from product_desc' always truncates characters more than 255 characters long. Even exporting the data to an Excel spreadsheet using DTS does the same thing.

Thank you
Praveena

View 5 Replies View Related

Query/select The First 10 Characters In A Field

Jul 25, 2007

I'm comparing two tables and need to compare the first 8 characters of one field in table A to the first 8 characters in another field in table B.

So instead of where 'John Smith' = 'John Smith' it would compare where 'John Smi' = 'John Smi'


I know I've done this before but can't find a good reference.

It's something like this when calling a query within a WHILE loop:

SELECT * FROM blah WHERE FID = '".$row['FID']."' AND LEFT('TRACK', 8) = LEFT('".$row['TRACK']."',8)


Am I close? Any help would be appreciated.

View 3 Replies View Related

Replace Invalid XML Characters Using SQL Query

Apr 21, 2008



Hi,
I am populating a dataset in .net with output from sql 2005 database. One of the columns in the table is a 'varchar(max)' type. This dataset is then converted to XML using WriteXml and written to a .xml document. But due to the presence of invalid characters, this process errors out.
Is there any way using which these invalid characters can be replaced at the database level itself when querying on the table?
The error that is produced is as follows:
'', hexadecimal value 0x1C, is an invalid character. Line 32201, position 924.

Thanks,
Nisha

View 14 Replies View Related

Transact SQL :: How To Query International Characters

Aug 14, 2015

i've worked with SQL Databases for years now, but never needed to deal with international characters.  I am trying to search strings like surname in a column that might have international letters/characters, such as:

select * from [dbo].[User] where LastName like '%HekimoÄŸlu%'

It doesn't retrieves anything.  I've googled for while in a hope to find some solution quickly, but to my surprise I couldn't. how to query string columns that include international characters such as above.I am using SQL Server 2012, Nvarchar(100) for column.

View 5 Replies View Related

How Can I Store Over 16000 Characters To Sql Table Field With Language Specific Characters?

Feb 19, 2008

In my application I must store over 16000 character in a sql table field . When I split into more than 1 field it gives "unclosed quotation mark" message.
How can I store over 16000 characters to sql table field (only one field) with language specific characters?
 
Thanks
 
 

View 3 Replies View Related

Handling Wildcard Characters In Query String

Nov 11, 2005

Hi

First interaction to the forum.
My Query is :

I had a User Management module in my application where I created a user with name

`~!@#$@%^&*()[_]+|}{":?><-=[[]];',./

Now I have a functionality to search for the user existing. For that give the search string or a single character and it finds out all the records containing the character.

How do I go about it as the SP i created for it gives correct results except the following

1. Search for % - Gives all record
2. Search for _ - Gives all records
3. Search for [ - Gives NO record
4. Search for the whole string - Gives NO Record

I handeled a few issues

1. replaced [ by [[]
2. replaced _ by [_]

So issues 2 & 3 are resolved.

Tried replacing % by [%] but did not work


Could someone plz help

Thanks in advance
Ashutosh

View 11 Replies View Related

Query To Identify Fields With Special Characters?

Aug 6, 2012

Basically, I'm trying to upload data from one system to another and the system which is receiving data is throwing error after loading few records. On further investigation I found out that the failing records has bad characters embedded. I was wondering if there is way to find out all the bad characters (special characters) in a table. I'm using MSSQL Server 2005 version. Tried using RegGenExpression but no luck.

View 1 Replies View Related

SELECT Query Involving Non-English Characters

Apr 8, 2008



Hello friends,

I am inserting non-english strings into my database table from my java program.



Code Snippet
sql = "insert into static_string1 values (?)";
PreparedStatement statement=connection.prepareStatement(sql);
statement.setString(1,statString);




where, statString is a string variable containing Hebrew characters.

Till here, my code works fine. i.e, Hebrew characters are properly inserted to the database.
The problem is when I try to retrieve the String_Id based upon the statString I inserted to the table static_string1.




Code Snippet
String sql = "select String_Id from Static_String1 where String like ('" + statString +"')";
Statement statement=connection.createStatement();
ResultSet rs=statement.executeQuery(sql);
rs.next();
int stringId=rs.getInt("String_Id");


I tried hardcoding the string in the query and to execute it from the SQL Server Management Studio as below



Code Snippetselect String_Id from Static_String1 where String like( ' ×”×–×—' );



But even this is returning null rows, even though the entry is present in the table Please help me out asap.


Please pardon me if this is not the right section to post my doubt. I didnt find any other relevant section here.

View 14 Replies View Related

Transact SQL :: Query - How To Check For Special Characters

Aug 21, 2015

query that checks to see if a variable contains special characters; except for hyphens, periods, and accents?

View 3 Replies View Related

Increasing The Number Of Characters Displayed In Query Analyzer

Feb 26, 2001

Hi,

I am using SQL Server 7.0.

I have a table with one column defined as Varchar(8000).

The table is populated with rows..

However when I do a Select * from the the Table in query analyzer I only see about 200 Characters even though each column has 8000 characters.

How can I increase the number of characters that are diplayed in the Query Analyzer?

Thankx

KP

View 1 Replies View Related

Max Vs 8000

May 2, 2008

Hello,
am using c# and a Stored proceedure in MS SQL 2005.
For data size i used the varchar (max) in my stored proceedure. But in the c# class. What do i use ? Here is a line of my code
thanks
Ehi
  command.Parameters.Add(new SqlParameter("@csv", SqlDbType.VarChar, 8000, "recipients")); 

View 3 Replies View Related

SQL Limit Of Demilted Pipe Characters Passed As Parameter To Sql Query

Nov 16, 2004

I am not sure about the architecture of the Issue Tracker and hence not sure if it applies here. But I will post in any case and wait for users on this forums comments as well.



===========Earlier post==================
This question is regarding the architecture of TimeEntry.
In some programs it builds an arrayList for Master-detail type of relationship and when user is ready to save it by clicking 'submit' it build a variable with pipe delimited fields.
This is then passed to a sql query.

This to me does not seem to be an efficient manner. Because the max character is 1500 chars as parameter to SQL query.


I was wondering if instead I could store it as an XML and then use the XML to import in to SQL.


Any ideas is greatly appreciated, I am running in to problems where my variable construct does increase to more than 1500 chars. Any thoughts are much appreciated in this regards.

Regards,
MillenniumIte.

View 2 Replies View Related

Need Variable More Than 8000

Aug 15, 2001

Hello,

We are using Exec(@sql) with @sql varchar(8000), but 8000 is not enough. Like query get cut off in the middle of the script. We need more than that. Is there any way to store more than 8000 characters in the variable ?
We use SQL Server 7.

Thanx in advance.

View 1 Replies View Related

Varchar(8000) Is Not Enough... What Can I Do?

Jan 25, 2008

Hi all!

I'm using this store proc to get a pivot table in SQL:


USE [m0851System]
GO
/****** Object: StoredProcedure [dbo].[sp_CrossTabIntoTable] Script Date: 01/25/2008 09:59:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_CrossTabIntoTable]
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
,@tbl_result varchar(100),
@fld_sufx varchar(10)
-- FIN JULIEN BONNIER
AS

--DECLARE @sql varchar(8000), @delim varchar(1)
DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
-- MODIFIÉ PAR JULIEN BONNIER 25 janvier 2008 (ajout de la clause case)
IF LEFT(@tbl_result,1)='#'
BEGIN
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
ELSE
BEGIN
IF EXISTS(SELECT name FROM sysobjects WHERE type='U' AND name='' + @tbl_result + '')
EXEC ('DROP TABLE ' + @tbl_result + '')
END
-- FIN JULIEN BONNIER

EXEC ('SELECT ' + @pivot + ' AS pvt INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pvt'

-- MODIFIÉ PAR JULIEN BONNIER 18 juillet 2007
--SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + ''' = ' +
SELECT @sql=@sql + '''' + convert(varchar(100), pvt) + '' + @fld_sufx + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pvt) + @delim + ' THEN ' ) + ', ' FROM ##pivot
-- FIN JULIEN BONNIER
-- AJOUTÉ PAR JULIEN BONNIER 15 octobre 2007
ORDER BY pvt
-- FIN JULIEN BONNIER

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
PRINT(LEN(@sql))
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')
-- AJOUTÉ PAR JULIEN BONNIER 17 juillet 2007
SELECT @select=stuff(@select, charindex(' FROM ', @select), 6, ' INTO ' + @tbl_result + ' FROM ')
-- FIN JULIEN BONNIER

--EXEC (@select)
PRINT('YOYO'+@select)
RETURN
SET ANSI_WARNINGS ON


But now my @sql and @select that are varchar(8000) get bigger than 8000 for one of my reports... So my query fails ever times.

What can I do to fix it ?

Thanks in advance.

Or Tho

View 14 Replies View Related

Character More Then 8000

Feb 21, 2008



Hi ?

i have created a store procedure that will copy the proceduers of one database to another database. it's working fine, but i am hving problem to run the the store proceduer when character lenght goes more then 8000 words. i have couple of proceuder that has words more then 8000, is there any good and short way to do solve this problem

Thanks and looking forward.

-MALIK

View 18 Replies View Related

Separate Lowercase Characters From Uppercase Characters

Mar 5, 2008


Hi everybody,
I would like to know if there is any property in sql2000 database to separate lowercase characters from uppercase characters. I mean not to take the values €˜child€™ and €˜Child€™ as to be the same. We are transferring our ingres database into sqlserver. In ingres we have these values but we consider them as different values. Can we have it in sqlserver too?

Hellen

View 1 Replies View Related

A Textstring More Than 8000 Chars.

Feb 1, 2001

Hi,
I have a problem with a text string which is more than 8000 chars.
I am taking this string as an input from an application.so,I cannot define a local variable as text or ntext and varchar has limitation of only 8000 char.
Can anyone help me in dealing with this situation.
Also,I cannot break the string at application level.I wish if I could solve it at db level somehow?
TIA
pd

View 5 Replies View Related

SQL Server DataType &> 8000

Nov 4, 2005

Is there a way to create a datatype other than varchar that is greater than 8000? :o

View 3 Replies View Related

REPLICATE() Beyond 8000 Bytes

May 18, 2007

I'm wondering if there's a way to use the string function replicate() to produce an output greater than 8000 bytes. From the documentation REPLICATE returns a varchar of max 8000 but I'm told there's a way to stop this restriction.

My mentor has been giving me tasks to try and accomplish and for this one I'm to create a stored procedure with no limitations, however I'm stuck at this replicate() problem.

Any help or guidance would be great.

Thanks

View 1 Replies View Related

8000 Records In Over 12 Minutes?

May 28, 2014

I have the following SQL select query:

Code:
SELECT
inbound_emails.inbound_email_id,
inbound_emails.campaign_id,
campaign_header.campaign_name,
customers.customer_name,
inbound_emails.date_received,

[Code] ....

Without any indexes (other than PK's) this query would take 1min 21 secs to return all 8500 records.

I wasn't satisfied with that so I added indexes to the tables.

Indexes are;

inbound_emails:

PK: inbound_email_id
1. campaign_id (non-unique, non-clustered)
2. email_from (non-unique, non-clustered)
3. email_to (non-unique, non-clustered)

campaign_header:

PK: campaign_id
1. campaign_name (unique, non-clustered)

customers:
PK: customer_id
1. customer_name (unique, non-clustered)

contracts:
PK: contract_id
1. contract_name (unique, non-clustered)
2. customer_id (non-unique, non-clustered)

Have I made a hash of the indexes? Or is the query statement badly written? What is a reasonable amount of time to expect to retrieve 8500 records?

I think email_from and email_to on inbound_emails is the main culprit, but I don't understand why?

View 11 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved